views:

424

answers:

6

hey everyone,

Im trying to create a view out of quite a complex select query and it wont let me put a clustered index on it because i have to use subqueries and some aggregate functions.

I have to get a clustered index on it otherwise the queries that use the view will take forever. Apparently sql server will only store the result set if u meet a stupid amount of criteria.

The base tables for the view are read-only and and only get updated by a bulk import once a day. I can't see why the results can't be cached.

Does anyone know of any way to get sql server to cache the results of a view so they can in turn be queried later?? I dont really want to create another table cos that would snowball into a bunch of changes all over the place.

Thanks in advance.

+1  A: 

The short answer is that a clustered index cannot be created, due to the reasons you mention.

When you ask for a way to cache the results of the complicated query, the only other object that SQL Server provides (and will solve your issue) is a table.

If automation is a problem, you should consider creating the view, but only using it as a way to insert into a table, such that you can do a truncate/insert into the table (selecting from the view) immediately after the bulk insert.

If you use SSIS (SQL Server Integration Services) this is a relatively trivial thing to add.

Jeff Meatball Yang
thanks man, more info in comments below
andrej351
A: 

As far as I'm aware, when compiling execution plans SQL Server essentially copies and pastes the definition of the view into the query its compiling - as long as you are able to add indexes to the underlying tables it should be possible to get good performance from the query.

Kragen
I don't see any reason to downvote this. With the information provided in the question, there is no way of knowing that the underlying tables are properly indexed or not.
Lieven
there is an additional issue of an ORM (Entity Framework) sitting on top of all this which adds more overhead. The query is made up of 3 left outer joins and 5 nested queries so if i get time, ill swap the table names out and post it
andrej351
If you also post an exectuion plan then the chances are someone here will be able to point out and bottleknecks. Also you might want to try putting the query through the index tuning wizard (if you haven't already done so)
Kragen
+1  A: 

What you are building sounds like a data warehouse, therefore your best option is to manipulate the data once it is in the system. You can build new tables of denormalised (or however else you are modifying it) and index them to allow quick querying.

You can then build views on top of these tables if you need to.

ck
Thanks for your advice as well, see comment below.
andrej351
A data warehouse might be worth investing in long term, maybe use my answer to get the short term fix, consider if a warehouse can help long term
Stuart
A: 

I think the answer you are looking for is: Don't use a view to do this. Use a table with the fields corresonding to the returned fields form the sql query . Automate the query to populate this table

Stuart
I think your right and thanks to everyone else who posted a suggestion. The system is a complement to a large, old CMS which fills in for some specific business needs. Im essentially massaging their poorly structured data to more acurately model the entities they're interested in.
andrej351
A: 

I had the same problem and ended up putting the sub queries in clustered index views themselves.

A: 

when using aggregates inside a indexed view you need to use COUNT_BIG() instead of COUNT() otherwise the view won't be created

Also if you are not on Enterprise Edition you need to provide the NOEXPAND hint otherwise the optimizer will not use the view

SELECT *
FROM YourView WITH(NOEXPAND)
WHERE ....

Maybe you don't need a view but you just don't have th correct indexes on the tables, can you post the DDL of the tables (including indexes and constraints)

SQLMenace