|
Hi All, 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
(comments are locked)
|
|
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.
(comments are locked)
|
|
What have you done in means of checking out the indexes or reviewing the execution plan of this query? Here is a pretty good article by Grant that you should take a look at on reading an execution plan and what various things mean. You might also want to download SQL Plan Explorer from SQL Sentry. Free tool and can download without having to give your email address. 100% free.
Aug 14 '12 at 03:05 PM
Tim
Actually, I wrote that article, not Tim.
Aug 14 '12 at 03:20 PM
Grant Fritchey ♦♦
Sorry Grant, must have been looking at one post by Tim and mixed thing up. At least the link was correct. :)
Aug 14 '12 at 03:31 PM
Tim
No worries. Just protecting the brand. ;-D
Aug 14 '12 at 03:43 PM
Grant Fritchey ♦♦
(comments are locked)
|
Hi I have seen Execution plan and it is showing as 50% and created non clustred index
USE [Psql-1aug-2012] GO
/****** Object: Index [inx_Trans] Script Date: 08/14/2012 20:04:50 ******/ CREATE NONCLUSTERED INDEX [inx_Trans] ON [dbo].[Transactions] ( [SiteId] ASC
) INCLUDE ( [Date], [Pump], [Qty], [Time], [TransNum], [AcctNum], [NetworkTrans], [card1], [card2], [PPG], [TransTotal], [Odometer], [VehcName], [ImportDate] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Logical Reads Reduced to 6326 , i have attached Execution plan also ,
please advice any indexes or someother
Posting an image of an estimated plan, and one that doesn't include any part of the actual query plan for the query in question, is largely a waste. If you want to post something, an actual plan using the .sqlplan file format is actually useful.
Posting the related structure might be helpful too. There are quite a few INCLUDE columns in that index. It suggests, possibly, that the table doesn't have a clustered index? If so, it doesn't seem like it's on a useful column.