Query Optimization using Indexes

Question

Post reply

Query Optimization using Indexes

Viewing 15 posts - 61 through 75 (of 110 total)

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

Mishal Ten Centuries Points: 1103 More actions · Answer 1

Mishal

Ten Centuries

Points: 1103

October 20, 2011 at 3:33 am

#1397158

it takes 58 sec to execute,

nd i am directly executing them into SSMS

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

Do you have results from the three test queries?

^{“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

Mishal Ten Centuries Points: 1103 More actions · Answer 3

Mishal

Ten Centuries

Points: 1103

October 20, 2011 at 3:49 am

#1397169

No there is no results...only messages indicating number of rows effected..

if we execute select * from #orderlines

then rows effected will display

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

adlakha.22 (10/20/2011)

No there is no results...only messages indicating number of rows effected..

if we execute select * from #orderlines

then rows effected will display

Yes, that's exactly as expected. How long did each query take? Can you post the plans please.

^{“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

Mishal Ten Centuries Points: 1103 More actions · Answer 5

Mishal

Ten Centuries

Points: 1103

October 20, 2011 at 4:10 am

#1397184

@chris-2

query which u hv posted, took 58 sec to execte

and select qry executed in 3 sec(Select * from #Orderlines).

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

adlakha.22 (10/20/2011)

@Chris

query which u hv posted, took 58 sec to execte

and select qry executed in 3 sec(Select * from #Orderlines).

Not quite right - let's try again. Here are three test queries. I need to know how long each one takes to run, then I need the execution plan for all three run together.

-- Test query 1



DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS

SELECT @ordernumber = ordernumber

FROM orderlines ol

INNER JOIN #prodline pl ON pl.prodline = ol.prodline

        AND oetype = 'R'

        and cono = '10'

        and enterdt >= '1/1/2008 12:00:00 AM'

        and enterdt < '10/25/2008'

        and transtype not in ('qu','RA','BR','CR','ST')

WHERE whse IN ('100','101','102','103','104','105','106','107','108','120','121','122','151')   

-- Test query 2

DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS

SELECT @ordernumber = ordernumber

FROM orderlines ol

INNER JOIN #prodline pl ON pl.prodline = ol.prodline

        AND oetype = 'R'

        and cono = '10'

        and enterdt >= '1/1/2008 12:00:00 AM'

        and enterdt < '10/25/2008'

        and transtype not in ('qu','RA','BR','CR','ST')

WHERE whse IN ('100','101','102','103','104','105','106','107','108','120','121','122','151')

GROUP BY ordernumber, ol.prodline, enterdt, operid, slsrepin

-- Test query 3

DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS

SELECT @ordernumber = ordernumber

FROM orderlines ol

INNER JOIN #prodline pl ON pl.prodline = ol.prodline

^{“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

Mishal Ten Centuries Points: 1103 More actions · Answer 7

Mishal

Ten Centuries

Points: 1103

October 20, 2011 at 5:08 am

#1397214

@chris-2,

1st query takes:46 sec

2nd one:44 sec

3rd one:43 sec

but i think this estimation is not always fix,

means very little diffrence not be same for every execution

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

So each one of the three takes around 40 to 50 seconds?

Last test: add this index, then run each query again, noting the time each query takes to run:

CREATE NONCLUSTERED INDEX [IX_TestIndex]

ON [dbo].[orderlines] ([prodline],[cono],[oetype],[transtype],[whse],[enterdt])

INCLUDE ([ordernumber])

^{“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

Mishal Ten Centuries Points: 1103 More actions · Answer 9

Mishal

Ten Centuries

Points: 1103

October 20, 2011 at 5:42 am

#1397244

1st qry takes-1 sec

2nd - 40-50 sec

3rd-3 sec

here is execution plan for combined

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

Well it's clear that a huge covering index would improve the performance of this query but the cost of maintaining it would be considerable. Here's a compromise.

CREATE NONCLUSTERED INDEX [IX_Testing] ON [dbo].[orderlines]

([prodline], [ordernumber],[enterdt],[oetype],[cono],[transtype],[operid],[slsrepin],[whse])

What I recommend you do is remove all the other testing indexes from the table, then create this one.

Then run the three test queries. What we're looking for is an improvement in the performance of the second one. If there is a significant performance boost, then run the main orderlines query and take a timing and EP.

^{“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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

October 20, 2011 at 6:11 am

#1397266

0.02$ that the filter returns too much data to consider a bookmark lookup ;-).

I'll say it again... all or nothing.

I applaud the effort tho 🙂

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

Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.

^{“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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

ChrisM@Work (10/20/2011)

Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.

It can't.

It's either 99% faster or it's still a clustered index scan. And with 700K rows returned that table must have over 25M - 140M rows (just a guess) to maybe make the bookmark lookup worth it.

If it helps it's to get better estimate... almost 4:1 underestimating this... not good!