x

SQL Seelect Query taking more Logical reads please suggest best optimization technique

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
SiteId AS 'Site ID',
TransNum AS 'Transaction#',
Convert(VARCHAR(10),Date,110) AS 'Date',
dbo.FormatDateTime(Time, 'HH:MM 12') AS Time,
CASE NetworkTrans WHEN '1' THEN CASE ISNULL(AcctNum,'') WHEN '' THEN 'Network Card' WHEN 'Global Card' THEN 'Network Card' ELSE Acctnum END ELSE Acctnum END AS 'Account ID',
card1 AS 'Driver/Single Card',
card2 AS 'Vehicle Card',
Pump AS 'Pump #',
Qty AS 'Quantity',
PPG AS 'Unit Price',
TransTotal AS 'Total',
Odometer,
VehcName AS 'Vehicle',
NetworkTrans AS 'Network Transaction',
ImportDate,
'COMPLETE' AS 'TransactionType',Date+[Time] as 'DateTime',CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS 'Time24hr' FROM Transactions JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate
AND Time= lst.TempTime WHERE lst.record_num between 1 and 50 AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL) UNION ALL SELECT DISTINCT
SiteId AS 'Site ID',
TransNum AS 'Transaction#',
Convert(VARCHAR(10),Date,110) AS 'Date',
dbo.FormatDateTime(Time, 'HH:MM 12') AS Time,
CASE NetworkTrans WHEN '1' THEN CASE ISNULL(AcctNum,'') WHEN '' THEN 'Network Card' WHEN 'Global Card' THEN 'Network Card' ELSE Acctnum END ELSE Acctnum END AS 'Account ID',
card1 AS 'Driver/Single Card',
card2 AS 'Vehicle Card',
Pump AS 'Pump #',
Qty AS 'Quantity',
PPG AS 'Unit Price',
TransTotal AS 'Total',
Odometer,
VehcName AS 'Vehicle',
NetworkTrans AS 'Network Transaction',
ImportDate,
'INCOMPLETE' AS 'TransactionType',Date+[Time] as 'DateTime',CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS 'Time24hr' FROM TransIncomplete JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate
AND Time= lst.TempTime WHERE lst.record_num between 1 and 50 AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL) ORDER BY CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) Asc
more ▼

asked Aug 14, 2012 at 02:28 PM in Default

sravan.434 gravatar image

sravan.434
20 7 7 9

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 ,alt textplease advice any indexes or someother

explan.jpg (104.4 kB)
Aug 14, 2012 at 02:53 PM sravan.434

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.
Aug 14, 2012 at 03:01 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Aug 14, 2012 at 02:50 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
What have you done in means of checking out the indexes or reviewing the execution plan of this query?
more ▼

answered Aug 14, 2012 at 02:43 PM

Tim gravatar image

Tim
36.4k 39 41 139

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, 2012 at 03:05 PM Tim
Actually, I wrote that article, not Tim.
Aug 14, 2012 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, 2012 at 03:31 PM Tim
No worries. Just protecting the brand. ;-D
Aug 14, 2012 at 03:43 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1854

asked: Aug 14, 2012 at 02:28 PM

Seen: 862 times

Last Updated: Aug 14, 2012 at 08:21 PM