Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by stekim, Aug 6, 2007.
It depends on what your query is. You could start with looking into indexes on the GROUP BY fields.
And if you've covered the indexes, then check out the following.
If you are SUMming one column, make sure it is a numeric data type, not alphabetic.
If you are SUMming on an expression that covers more than one column - like SUM(colA + colB) or SUM(CASE WHEN blabla THEN colA ELSE colB END) - make sure that the columns are of the same numeric data type.
Also you should cover to check other aspects such as hardware issues or any other contention based problems that are contribting to this slow performance.
Continue your discussion here as other thread is locked.
The hardware can help but the aggregation like sum seems to not use the cache.
I created temporary table and used that as my bases for the query and it returned the result set in 1 sec where the original query took 30 sec.
Thus, the ultimate question is when I run the aggregation select statement twice, does the result set get cached?
Say in a low latency application, the caches introduce a risk of query result inconsistency. Imagine that two queries are executed simultaneously, one at an aggregate level and the second at a detailed level. So imagine that the aggregate query is answered from the local or server cache, but the detailed query must be resolved from the database level. Any new transactions will be picked up by the detailed query, and an aggregation of the detailed data may be inconsistent with the aggregate query resolved from cache. There is no way to turn off caching; the caches must be flushed on a frequent schedule.
Obviously the performance is degraded in a low latency because of caching issues too.
You could add a view that does the SUMming for you.
Interesting question about the data cache - with aggregate queries, does the underlying data get cached, or also the aggregate results?
Query results themselves do not get cached, but the qualifying pages do.
Separate names with a comma.