Query Optimization using Indexes

Question

Post reply

Query Optimization using Indexes

Viewing 15 posts - 91 through 105 (of 110 total)

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

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

adlakha.22 (10/24/2011)

Hi Chris,

I was trying to use Suggestion given in your last post,

Could u plz guide me further...how to move on that Qry..

Sure. Are you on UK time by any chance?

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

ChrisM@Work (10/20/2011)

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

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

ChrisM@Work (10/20/2011)

... 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 4

Mishal

Ten Centuries

Points: 1103

October 24, 2011 at 2:50 am

#1398546

Thanks for Reply Chris

I have executed all the 3 queries

and got these results

1st qry took:1sec

2nd-2 sec

3rd-2 sec

as u were expected ,here is improvement in execution time for 2nd Qry

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

adlakha.22 (10/24/2011)

Thanks for Reply Chris

I have executed all the 3 queries

and got these results

1st qry took:1sec

2nd-2 sec

3rd-2 sec

as u were expected ,here is improvement in execution time for 2nd Qry

Very promising!

Next is this - please run as one batch, logging the time, and post the plan:

SELECT *



INTO #prodline

FROM (

        SELECT prodline = '####' UNION ALL

        SELECT '100' UNION ALL

        SELECT '101' UNION ALL

        SELECT '3M' UNION ALL

        SELECT '80NIPA' UNION ALL

        SELECT '80NIPN' UNION ALL

        SELECT '80TOE' UNION ALL

        SELECT '80UNI' UNION ALL

        SELECT 'AGRIFI' UNION ALL

        SELECT 'BF' UNION ALL

        SELECT 'BRNIP' UNION ALL

        SELECT 'BRPIPE' UNION ALL

        SELECT 'BULBS' UNION ALL

        SELECT 'BWF' UNION ALL

        SELECT 'CALS' UNION ALL

        SELECT 'COEXPI' UNION ALL

        SELECT 'COMCOU' UNION ALL

        SELECT 'CONPIP' UNION ALL

        SELECT 'COPFIT' UNION ALL

        SELECT 'CORFIT' UNION ALL

        SELECT 'CORPIP' UNION ALL

        SELECT 'CV/UCV' UNION ALL

        SELECT 'DRFIT' UNION ALL

        SELECT 'DRFITS' UNION ALL

        SELECT 'DRITUB' UNION ALL

        SELECT 'DRPIPE' UNION ALL

        SELECT 'ECOBV' UNION ALL

        SELECT 'ENCLOS' UNION ALL

        SELECT 'FERT' UNION ALL

        SELECT 'FIXT' UNION ALL

        SELECT 'FLAG' UNION ALL

        SELECT 'FLG' UNION ALL

        SELECT 'FLOCON' UNION ALL

        SELECT 'GALFIT' UNION ALL

        SELECT 'GALNIP' UNION ALL

        SELECT 'GALPIP' UNION ALL

        SELECT 'GASFIT' UNION ALL

        SELECT 'HONWEL' UNION ALL

        SELECT 'HYDSAF' UNION ALL

        SELECT 'IMPBRS' UNION ALL

        SELECT 'INSFIT' UNION ALL

        SELECT 'JCM' UNION ALL

        SELECT 'KCKSC' UNION ALL

        SELECT 'LP' UNION ALL

        SELECT 'LVWIRE' UNION ALL

        SELECT 'MCWIR' UNION ALL

        SELECT 'MCWIRC' UNION ALL

        SELECT 'OLYM' UNION ALL

        SELECT 'PARTS' UNION ALL

        SELECT 'PTS' UNION ALL

        SELECT 'PULBOX' UNION ALL

        SELECT 'PVCPIP' UNION ALL

        SELECT 'RBMAXI' UNION ALL

        SELECT 'RBPUMP' UNION ALL

        SELECT 'REBAR' UNION ALL

        SELECT 'RECPIP' UNION ALL

        SELECT 'RMCENT' UNION ALL

        SELECT 'RTPIPE' UNION ALL

        SELECT 'S40FIT' UNION ALL

        SELECT 'S80FIT' UNION ALL

        SELECT 'SATASS' UNION ALL

        SELECT 'SCWIRE' UNION ALL

        SELECT 'SEED' UNION ALL

        SELECT 'SENN' UNION ALL

        SELECT 'SLIFIX' UNION ALL

        SELECT 'SOCK' UNION ALL

        SELECT 'SOD' UNION ALL

        SELECT 'STAR' UNION ALL

        SELECT 'STDASS' UNION ALL

        SELECT 'STPWIR' UNION ALL

        SELECT 'TIMER' UNION ALL

        SELECT 'TRAN' UNION ALL

        SELECT 'UVRFIT' UNION ALL

        SELECT 'UVRPIP' UNION ALL

        SELECT 'VITPRO' UNION ALL

        SELECT 'WATBOX'

) d

CREATE UNIQUE CLUSTERED INDEX [CX_prodline] ON #prodline (prodline) -- NEW #####

IF OBJECT_ID('tempdb..#orderlines') IS NOT NULL DROP TABLE #orderlines -- new #####

SELECT

    [Order#]                = ordernumber,

    [Cust#]                 = MAX(custno),

    [OE Type]               = DBO.GETOETYPE(MAX(oetype)),

    [Trans]                 = MAX(transtype),

    [Whse]                  = MAX(whse),

    [Ship To ]              = isnull(convert(varchar,MAX(shipto),101),''),

    [Qty Ordered]                       = sum(qtyord),

    [Qty Shipped]                       = sum(qtyshp),

    [Line Amount]                       = cast(sum(lineamt) as decimal(19,2)),

    [Ordered Amt]                       = cast(sum(ordamt) as decimal(19,2)),

    [Shipped Amt]                       = cast(sum(shpamt) as decimal(19,2)),

    [Avg Cost]              = CAST(sum(avgcost) as money),

    [STD Cost]              = CAST(sum(stdcost) as money),

    [Addon Cost]                        = CAST(sum(addcost) as money),

    [Lnd Cost]              = CAST(sum(landcost) as money),

    [Profit]                = cast((sum(lineamt) - sum(landcost)) as decimal(19,2)),

    [Profit%]               = cast (

            (select case

            when sum(lineamt) = 0 and  sum(landcost) <> 0 then -100

            when  sum(landcost) = 0 then NULL

            else   (1 - sum(landcost)/sum(lineamt)) * 100 end)

            as decimal(19,2)),

    [Enter Date]                        = convert(varchar, MAX(enterdt), 101),

    [Oper ID]               = MAX(operid),

    [SalesRep In]                       = MAX(slsrepin),

    [SalesRep Out]                      = MAX(slsrepout),

    [TakenBy]               = MAX(takenby),

    [ProdLine]              = MAX(ol.prodline)

INTO #orderlines  -- new #####

FROM orderlines ol  -- with (index(Ix_New1)) <<---- avoid index hints - they are very rarely necessary!!

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

ORDER BY ordernumber, ol.prodline, enterdt, operid, slsrepin -- NEW #####

DROP TABLE #prodline -- NEW #####

CREATE UNIQUE CLUSTERED INDEX [CX_orderlines] ON #orderlines (ordernumber, prodline, enterdt, operid, slsrepin) -- NEW #####

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

Mishal

Ten Centuries

Points: 1103

October 24, 2011 at 3:18 am

#1398556

Chris

I have executed Qry, and it took 71 sec. to execute

U can see EP.

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

adlakha.22 (10/24/2011)

Chris

I have executed Qry, and it took 71 sec. to execute

U can see EP.

Two sorts - 70% of total.

Comment out this line:

[OE Type] = DBO.GETOETYPE(MAX(oetype)),

Change

[Profit%] = cast (



            (select case

            when sum(lineamt) = 0 and  sum(landcost) <> 0 then -100

            when  sum(landcost) = 0 then NULL

            else   (1 - sum(landcost)/sum(lineamt)) * 100 end)

as decimal(19,2)),

to

[Profit%] = cast (



            (CASE

            when sum(lineamt) = 0 and  sum(landcost) <> 0 then -100

            when  sum(landcost) = 0 then NULL

            else   (1 - sum(landcost)/sum(lineamt)) * 100 end)

as decimal(19,2)),

i.e. remove the SELECT.

If there's any significant difference from the previous run, then please post timings and 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

Mishal Ten Centuries Points: 1103 More actions · Answer 8

Mishal

Ten Centuries

Points: 1103

October 24, 2011 at 4:32 am

#1398581

By executed this I got an error:

Warning: Null value is eliminated by an aggregate or other SET operation.

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

Ninja's_RGR'us

SSC Guru

Points: 294069

October 24, 2011 at 4:39 am

#1398584

adlakha.22 (10/24/2011)

By executed this I got an error:

Warning: Null value is eliminated by an aggregate or other SET operation.

That's not an error... just a warning.

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

adlakha.22 (10/24/2011)

By executed this I got an error:

Warning: Null value is eliminated by an aggregate or other SET operation.

Remi's right - ignore this for now.

^{“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 24, 2011 at 4:49 am

#1398594

Ok, I just got to ask.

Why are you doing this 3 times in a row?

SELECT @ordernumber = ordernumber

Either we are missing part of the code or the logic or you just don't want to return the data in your speed test.

P.S. Glad to see an new index actually killed it in perf :Whistling:

Mishal Ten Centuries Points: 1103 More actions · Answer 12

Mishal

Ten Centuries

Points: 1103

October 24, 2011 at 4:58 am

#1398598

oops........

sorry....by mistake ,i did't see this is warning

i will just place new execution paln here

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

Why are you doing this 3 times in a row?

SELECT @ordernumber = ordernumber

I didn't want the return of the data as you put it influencing the speed test. For your purposes, the difference in timing between query 2 and the full orderlines query is the improvement which could be realised by implementing the covering index with all of those included columns.

Ninja's_RGR'us (10/24/2011)

P.S. Glad to see an new index actually killed it in perf :Whistling:

Yep - 4 pages ago we had 30 seconds (with a very similar index), now it's gone backwards, 60 or 70 seconds.

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

Mishal

Ten Centuries

Points: 1103

October 24, 2011 at 5:05 am

#1398602

here is execution plan for covered index

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

adlakha.22 (10/24/2011)

here is execution plan for covered index

Can you post the CREATE INDEX statement?

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