CODEWITHSUNDEEP
asp.netsqlperformancecachingsql-server-2008-r2
O.O
O.O

Reputation: 11317

Strategy for handling variable time queries?

I have a typical scenario that I'm struggling with from a performance standpoint. The user selects a value from a dropdown and clicks a button. A stored procedure takes that value as an input parameter, executes, and returns the results to a grid. For just one of the values ('All'), the query runs for roughly 2.5 minutes. For the rest of the values the query runs less than 1ms.

Obviously, having the user wait for 2.5 minutes just isn't going to fly. So, what are some typical strategies to handle this?

Some of my own thoughts:

Any help is appreciated.

Thanks!

Update

A little bit more info:

sp returns two result sets. The first is a group by rollup summary and the second is the first result set, disaggregated (roughly 80,000 rows).

Upvotes: 1

Views: 56

Answers (2)

Patrick
Patrick

Reputation: 7722

Caching data is a good thing, but.... if the SP is inherently flawed, then you might want to actually fix it instead of trying to bandage it with caching.

You might also want to (since you didn't mention here) look at the number of rows "All" returns compared to the other selections and think about your indexes.

Also in your SP does the "All" cause it to run a different sets of tsql as in maybe a case or an if... or is it running the same code just with a different "WHERE"?

It might simply be that "ALL" just returns A LOT of records. You may want to implement paging and partial dataset return using ajax... (kinda like return the first 1000 records early so that it can be displayed and also show a throbber on the screen while the rest of the dataset is returned)

These are all options... if the number of records really isnt that different between ALL and the others... then it probably has something to do with the query/index/program flow.

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85116

I would first look at if your have the proper indexes in place. Using the Query Analyzer and the Database Tuning Assistant is a simple and often effective way of seeing what indexes might help.

If you still have performance problems after creating the appropriate indexes you might then look at adding tables/views to speed things up. If your query does a lot of joins you might consider creating an indexed view that allows you to do a select with no joins on the denormalized data. Since indexed views are persisted you can see big gains from their use.

You can read up on indexed views here:

http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx

and read about the database tuning adviser here:

http://msdn.microsoft.com/en-us/library/ms166575.aspx

Also, how many records does "All" return? I have seen people get hung up on the "All" scenario before, but if it returns 1 million records or something then the data is not usable to a person anyways...

Upvotes: 2

Related Questions