Saving / Caching Stored Procedure results for better performance? (SQL Server 2005)

Question

I have a SP that has been worked on my 2 people now that still takes 2 minutes or more to run. Is there a way to have these pre run and stored in cache or somewhere else so when my client needs to look at this data in a web browser he doesn't want to hang himself or me?

I am no where near a DBA so I am kind of at the mercy of who I hire to figure this out for me, so having a little knowledge up front would really help me out.

Answer 1

If it truly takes that long to run, you could schedule the process to run using SQL Agent, and have the output go to a table, then change the web application to read the table rather than execute the stored procedure. You'd have to decide how often to run the refresh, and deal with the requests that occur while it is being refreshed, but that can be dealt with as well by having two output files, one live and one for the latest refresh.

But I would take another look at the procedure, look at the execution plan and see where it is slow, make sure it is not doing full table scans.

Answer 2

Try "Analyze query in database engine tuning advisor" from the Query menu. I usually script the procedure to a new window, take out the query definition part and try different combinations of temp tables, regular tables and table variables.

Answer 3

You could cache the result set in the application as opposed to the database, either in memory by keeping an instance of the datatable around, or by serializing it to disk. How many rows does it return? Is it too long to post the code here?

Answer 4

Preferred solutions in this order:

1. Analyze the query and optimize accordingly
2. Cache it in the application (you can use httpRuntime.Cache (even if not asp.net application)
3. Cache SPROC results in a table in the DB and then add triggers to invalidate the cache (delete the table) so a a call to the SPROC would first look to see if there is any data in the cache table. If none, run SPROC and store the result in the cache table, if so, return the data from that table. The triggers on the "source" tables for the SPROC would just delete * from CacheTable to "clear the cache" (depending on what you sproc is doing and its dependencies, you may even be able to partially update the cache table based on the trigger, but all of this quickly gets difficult to maintain...but sometimes you gotta do what you gotta do...This approach will allow the cache table to update itself as needed. You will always have the latest data and the SPROC will only run when needed.

Answer 5

OK first things first, what indexes do you have on the tables and is the execution plan using them? Do you have indexes on all the foreign key fields?

Second, does the proc use any of the following performance killers: a cursor

a subquery

a user-defined function

select *

a search criteria that starts with a wildcard

third can the where clause be rewritten to be sargeable? There is more than one way to write almost everything and some ways are better performers than others.

I suggest you buy your developers some books on performance tuning.

Likely your proc can be fixed, but without seeing the code, it is hard to guess what the porblems might be.

Answer 6

hi You can Use Ramdisk software for keep your data in ram!!!!