How to Speed Up This Indexed View

How can i speed up this Indexed View?

What edition of SQL Server? I believe that only Enterprise and Developer Edition will use indexed views automatically, while the others support it using query hints.

SELECT a.PostId
FROM PostsCleanSubjectView a WITH (NOEXPAND)
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

From Query Hints (Transact SQL) on MSDN:

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

Speed up view performance

One improovement is to rewrite where clause into sargable clause. Add index to SubmitDate if there is no index and change query to:

FROM TableA
CROSS JOIN ViewA
INNER JOIN TableB on ViewA.Name = TableB.Name
   AND TableA.Code = TableB.Code
   AND TableA.Location = TableB.Location
WHERE 
TableA.SubmitDate >=DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0) 
And TableA.SubmitDate < Dateadd(DAY, 1, DATEADD(MONTH, 
  DATEDIFF(MONTH, -1, GETDATE())-1, -1) )

Also add nonclustered indexes on Name, Code and Location columns.

Makes it sense to create an indexed view when the data is frequently changed?

This is not a definite answer, rather just some general ideas to consider.

An important piece of information is what indexes the 5 underlying tables have. If those indexes are helpful for the view, then the query optimizer would use them, thus an index on the view might not make the view faster.

Based on just the information you're giving us, the main questions are the business needs, which would help to determine your database architecture: how often the tables are updated, how often the view is queried, how fast the updates and view query need to perform, and how current the view data needs to be.

If the view query performance is more important than the speed of the table updates, then use an indexed view.
If the view data can be cached and not current, and if the table update speed is important, then you could periodically copy all of the table data into a reporting table, such as once per day or hour. On that reporting table, you could create an index for fast read querying.
If the view performance is not important, but that view data has to be current, and if table update speed is important, then you probably have to use a non-indexed view, and maybe rely on table scans for every view query.

How to speed up queries with index design for a certain table?

The best you can do to index this query is an index on (type, date_num). Don't store timestamps as numbers.

Speeding up a SQL query with indexes

The best thing to do would depend on what other fields the table has and what other queries run against that table.

Without more details, a non-clustered index on (code, company, createddate) that included the "price" column will certainly improve performance.

CREATE NONCLUSTERED INDEX IX_code_company_createddate
ON Products(code, company, createddate)
INCLUDE (price);

That's because if you have that index in place, then SQL will not access the actual table at all when running the query, as it can find all rows with a given "code, company, createddate" in the index and it will be able to do that really fast as the index allows precisely for fast access when using the fields that define the key, and it will also have the "price" value for each row.

Regarding the inserts, for each row added, SQL Server will have to add them to the index as well, so performance for inserts will be impacted. In think you should expect the gains on SELECT performance to outweigh the impact on the inserts, but you should test that.

Also, you will be using more space as the index will store all those fields for each row besides the space used by the original table.

As others have noted in the comments, adding a PK to your table (even if that means adding a ProductId column you don't actually need) might be a good idea as well.

How to speed up this SQL index query?

What indexes do you have now?

Is it really "slow"? What evidence do you have?

Comments on "SELECT * instead of SELECT Employee.salary" --

* is bad form because tomorrow you might add a column, thereby breaking any code that is expecting a certain number of columns in a certain order.
Dealing with * versus salary does not happen until after the row(s) is located.
Locating the row(s) is the costly part.
On the other hand, if you have INDEX(salary) and only look at salary then the index is "covering". That means that the "data" (the other columns) does not need to be fetched. Hence, faster. But this is probably beyond what your teacher has told you about yet.

Comments on "the index on salary is non-clustered, and we want to use a clustered index" --

In MySQL (not necessarily in other RDBMSs), InnoDB has exactly one PRIMARY KEY and it is always UNIQUE and "clustered".
That is, "clustered" implies "unique", which seems inappropriate for "salary".
In InnoDB a "secondary key" implicitly includes the column(s) of the PK (ssn?), with which it can reach over into the data.

"verified that the query plan" -- Have you learned about EXPLAIN SELECT ...?

More Tips on creating the optimal index for a given SELECT.

Is a view faster than a simple query?

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

Microsoft's own documentation makes it very clear that Views can improve performance.

First, most views that people create are simple views and do not use this feature, and are therefore no different to querying the base tables directly. Simple views are expanded in place and so do not directly contribute to performance improvements - that much is true. However, indexed views can dramatically improve performance.

Let me go directly to the documentation:

After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

Second, these indexed views can work even when they are not directly referenced by another query as the optimizer will use them in place of a table reference when appropriate.

Again, the documentation:

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

This documentation, as well as charts demonstrating performance improvements, can be found here.

Update 2: the answer has been criticized on the basis that it is the "index" that provides the performance advantage, not the "View." However, this is easily refuted.

Let us say that we are a software company in a small country; I'll use Lithuania as an example. We sell software worldwide and keep our records in a SQL Server database. We're very successful and so, in a few years, we have 1,000,000+ records. However, we often need to report sales for tax purposes and we find that we've only sold 100 copies of our software in our home country. By creating an indexed view of just the Lithuanian records, we get to keep the records we need in an indexed cache as described in the MS documentation. When we run our reports for Lithuanian sales in 2008, our query will search through an index with a depth of just 7 (Log2(100) with some unused leaves). If we were to do the same without the VIEW and just relying on an index into the table, we'd have to traverse an index tree with a search depth of 21!

Clearly, the View itself would provide us with a performance advantage (3x) over the simple use of the index alone. I've tried to use a real-world example but you'll note that a simple list of Lithuanian sales would give us an even greater advantage.

Note that I'm just using a straight b-tree for my example. While I'm fairly certain that SQL Server uses some variant of a b-tree, I don't know the details. Nonetheless, the point holds.

Update 3: The question has come up about whether an Indexed View just uses an index placed on the underlying table. That is, to paraphrase: "an indexed view is just the equivalent of a standard index and it offers nothing new or unique to a view." If this was true, of course, then the above analysis would be incorrect! Let me provide a quote from the Microsoft documentation that demonstrate why I think this criticism is not valid or true:

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes.

Together with the above quote regarding the persistence of data in physical storage and other information in the documentation about how indices are created on Views, I think it is safe to say that an Indexed View is not just a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.