Query optimization help

Question

Post reply

Query optimization help

EasyBoy

SSCertifiable

Points: 5301
More actions
April 15, 2014 at 3:12 am

#287677

I have one simple query to optimize but somehow after creating couple of index this query is still having more logical reads and time.
DECLARE @CURRENT smalldatetime
set @current = '6/30/2013'
select Key, MAX(dateended) DateEnded
from InternalEstimate
where dateended< @Current and ms = 1 and Upd<2
group by Key
I have created index as
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OperateEstimate] ([Ms],[DateEnded],[Upd])
INCLUDE ([Key])
GO
But still logical reads are about 66,000 and taking 7-8 second to execute.
Any help would be greatly appreciated.
Need urgently.

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table definitions, index definitions and execution plan please.

How many rows qualify for that where clause predicate?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 2

I have attached execution plan.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OperatorCBGEstimate'. Scan count 3, logical reads 66974, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Total rows affected by where clause: 1099.

Please let me know if more information is required.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table definitions and index definitions please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 4

Please find the attached excel sheet for more information.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Your query filters out a very small number of rows from the table - you may as well scan them all from an index ordered by , which should give you a fast streaming aggregate instead of a slower hash aggregate. Something like this should do the trick:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[OperateEstimate] ([Key])

INCLUDE ([Ms],[DateEnded],[Upd])

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

EasyBoy SSCertifiable Points: 5301 More actions · Answer 6

EasyBoy

SSCertifiable

Points: 5301

April 15, 2014 at 6:15 am

#1706057

Does not make any difference.

Getting same result.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ChrisM@Work (4/15/2014)
Your query filters out a very small number of rows from the table - you may as well scan them all from an index ordered by , which should give you a fast streaming aggregate instead of a slower hash aggregate. Something like this should do the trick:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OperateEstimate] ([Key])
INCLUDE ([Ms],[DateEnded],[Upd])

This might be slightly better

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[OperateEstimate] ([Ms],[Key])

INCLUDE ([DateEnded],[Upd])

Filter on the equality and still have the resulting rows in order for the stream aggregate (which adding the other columns as index keys will prevent)

Worth trying

How many rows are in the table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 8

I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

Did you look at the execution plans to see if the hash aggregate was replaced with a stream aggregate? If you're not sure, then create the index which Gail suggests and post the execution plan here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

You're retrieving 14+ million rows from one index or another on a table containing 15+ million rows. Whichever of these alternative indexes is used, they all contain the same columns so the count of logical reads isn't likely to differ much between them, and is about as low as it can get. That doesn't mean the query cannot be made to run faster - it means you have to look elsewhere in the query plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

EasyBoy SSCertifiable Points: 5301 More actions · Answer 11

ChrisM@Work (4/16/2014)
EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).
Did you look at the execution plans to see if the hash aggregate was replaced with a stream aggregate? If you're not sure, then create the index which Gail suggests and post the execution plan here.

Please find the updated execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

How many rows total in the table?

You told me earlier that there were 1099 rows which qualified for the where clause, but the plan shows 14 million rows being returned from the filtered index seek. That's a large difference.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 13

Table contains total 14208765 rows but query itself fetching total 1099 rows.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

The query is reading just over 14 million rows and aggregating that down to 1099. If it's reading 14 million rows from a table (the rows which satisfy the where clause), the reads you're seeing are pretty much expected (you're reading the majority of the table) and hence you may not be able to reduce it much more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass