My Query below is taking more Logical Reads please suggest alternative to it
Table 'Transactions'. Scan count 1, logical reads 14570, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(415461 row(s) affected) 2 SELECT DISTINCT
asked Aug 14 '12 at 02:28 PM in Default
Two things immediately jump out at me. First, the use of the DISTINCT clause. This is an aggregation clause that will absolutely add to the load this query places on the server. Why do you need the DISTINCT statement? It's frequently used either out of habit because of previous poor query writing, or as a crutch because of bad data or bad structures. If you have bad data or bad structures, fix them. Don't hamstring your queries by forcing aggregation on every query, especially the ones that have no aggregation built in (such as this one). Next, the function on the ORDER BY statement will prevent any indexes that might exist for that column from being used, hurting performance.
Also, simply measuring logical reads and not taking into account both CPU and execution time is not the right way to tune queries. You can have very low logical reads, but high execution time. All aspects of the query must be taken into account.
answered Aug 14 '12 at 02:50 PM
Grant Fritchey ♦♦
What have you done in means of checking out the indexes or reviewing the execution plan of this query?
answered Aug 14 '12 at 02:43 PM