Only update view every 10 minutes..

[Rebies]

Hi, I have a view that takes about 4 seconds to run. However, I would like to only update the view once every 10 minutes so I can query off of that view and have the SQL query only take milliseconds instead of 4023 milliseconds.

Is there a way to do this with a view? Or should I maybe be creating a table and update it every 10 minutes?

I have looked at the estimated execution plan in query analyzer and it does not help much. This view is fairly complex and the SQL queries I will run off of that temporary table are extremely simple and should not take much time at all.

Thanks for any info. Still learning the ways of SQL Server!

Andrew

 

[SQLSister]

If you post your code we can help you optimize it. Using a view won't speed things up. Not sure why you are updating the view every ten minutes. If the data in the base tables changes, it will be reflected in the view the next time you query it. If you are running bulk data updates that frequently perhaps you need to investigate replication? Maybe you need to better explain what you are trying to accomplish.

 

[Rebies]

Well, more or less its a simple query that union's about 50 tables I am keeping seperate because each table has about 10 custom columns that do not match up with the other tables.

Each table has anywhere from 4,000 to 250,000 records. So the total returned results from the view will wind up with around 5 million records

SELECT 1 AS MerchantID, ProdAN, ProdName, ProdDescription, Category, Price, Thumbnail, LinkURL FROM MerchOne
UNION
SELECT 2 AS MerchantID, ProdAN, ProdName, ProdDescription, Category, Price, Thumbnail, LinkURL FROM MerchTwo
UNION
SELECT 3 AS MerchantID, ProdAN, ProdName, ProdDescription, Category, Price, Thumbnail, LinkURL FROM MerchThree

... etc, for all of the tables.

Since these tables are only updated at a maximum of once every 10 minutes, I am looking to possibly build a temporary table that stores all of this information so I can run other SQL queries directly off of the pre-built table. I am guessing that a view is not the way to do this though, am I right? What might be a better way of doing something like this then?

I guess I was hoping for a command in a view that says "Cache these results for 10 minutes, then update the table built again"

Andrew

 

[Rebies]

Or could it possibly be that a table of 5 million rows is getting high enough that that will significantly decrease database performance? More or less the types of queries I will run on this large table (or view for that matter) are:

SELECT COUNT(ProdAN) AS ReturnedProducts
FROM AllProducts
WHERE (Price BETWEEN 0 AND 500)

 

[sguslan]

A trigger would be helpful. Create a special table then create triggers on all source tables to insert/update the special table. There is concern about performance though. You may have to test and that it does not hamper write performance on the server ....