Query Optimization using Indexes

  • 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 (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 (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

  • 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

  • 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

  • Chris

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

    U can see EP.

  • 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

  • By executed this I got an error:

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

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

  • 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

  • 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:

  • oops........

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

    i will just place new execution paln here

  • 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

  • here is execution plan for covered index

  • 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

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

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