Query Optimization using Indexes

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 17, 2011 at 6:31 am

  #1395268

  Unless you want to include pretty much every other columns in that table, that looks like the most-ish plan you can expect.

  Might be worth it, might now. I don't know what constraints you have on this system.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 17, 2011 at 6:43 am

  #1395278

  Comment out the index hint!

  FROM orderlines ol -- with (index(Ix_Prodline)) <<-- keep this out of the query!!

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

  October 17, 2011 at 6:47 am

  #1395279

  Now try this:

  IF OBJECT_ID('tempdb..#prodline') IS NOT NULL DROP TABLE #prodline

  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 CLUSTERED INDEX [CX_prodline] ON #prodline (prodline)

  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)

  FROM orderlines ol -- with (index(Ix_Prodline)) <<-- keep this out of the query!!

  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

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

  October 17, 2011 at 6:49 am

  #1395280

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

  ---

  Unless you want to include pretty much every other columns in that table, that looks like the most-ish plan you can expect.

  Might be worth it, might now. I don't know what constraints you have on this system.

  Thanks for dropping by Remi, a second opinion - especially yours, is much appreciated. I reckon we've got a ways to go yet. The first pull from the orderlines table is still a full table scan, should be reasonably straightforward getting that down by 6-fold.

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

  October 17, 2011 at 7:32 am

  #1395313

  This query is executed in 55 sec.

  and at the same time i again execute the query with Ix_new that was taking 30 sec,(may be there was a n/w problem at my end)

  and execution plan for ur recent posted query is attached

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 17, 2011 at 7:37 am

  #1395322

  You guys play with this all you want. You can grapple seconds left and right.

  You want quantum leap improvement? You make that index with all the included columns.

  The only question is are you ready to pay that price on the system?

  Easy way to test is to put it out there and come back later to see the real cost. Keep in mind that most systems I tune are severly overpowered so I can use more of a shotgun approach and see what sticks.

  You need to measure if all the smalls updates to that index justify the massive gain on that query. It might, might not.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 17, 2011 at 7:41 am

  #1395325

  adlakha.22 (10/17/2011)

  ---

  This query is executed in 55 sec.

  and at the same time i again execute the query with Ix_new that was taking 30 sec,(may be there was a n/w problem at my end)

  and execution plan for ur recent posted query is attached

  Almost there. Change the new index as follows:

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

  (

  [Prodline] ASC,

  [Cono] ASC,

  [oetype] ASC,

  [transtype] ASC,

  [Whse] ASC,

  [enterdt] ASC)

  Make sure Prodline is the first item in the list - this is important.

  When you're done, post the 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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 17, 2011 at 7:50 am

  #1395333

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

  ---

  You guys play with this all you want. You can grapple seconds left and right.

  You want quantum leap improvement? You make that index with all the included columns.

  The only question is are you ready to pay that price on the system?

  Easy way to test is to put it out there and come back later to see the real cost. Keep in mind that most systems I tune are severly overpowered so I can use more of a shotgun approach and see what sticks.

  You need to measure if all the smalls updates to that index justify the massive gain on that query. It might, might not.

  I see your point, Remi - but all those included columns constitute almost the entire row. IAC the double-dip to collect columns has gone now 😎

  Setting Prodline as the leading edge of the new index should cut down the cost of the HMIJ by changing to a NLIJ. Check out the next plan our exceptionally good OP provides.

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

  October 17, 2011 at 7:52 am

  #1395335

  I'd be most happy to be proven wrong... 😉

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 17, 2011 at 7:55 am

  #1395340

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

  ---

  I'd be most happy to be proven wrong... 😉

  Heh we both know that's not going to happen! BUT - I reckon I can get close to your solution without the significant overhead of all those include columns. It's a fairly large table, remember...

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

  October 17, 2011 at 8:00 am

  #1395345

  Easy way to test is to put it out there and come back later to see the real cost. Keep in mind that most systems I tune are severly overpowered so I can use more of a shotgun approach and see what sticks.

  I'm able to add 2 GB of indexes here without anybody noticing :-D.

  Of course might not be possible there.

  The real question is assuming we can, how big is the gain compared to the rather <big &> constant cost of maintaining it? And is it worth it for that query? Any other queries could benefit from that index?

• Mishal

  Ten Centuries

  Points: 1103

  More actions

  October 18, 2011 at 12:28 am

  #1395705

  @chris-2

  Here it is New Execution Plan For Query with Index having Prodline Column at the Top,

  Now this Query is taking 35 sec to exceute before it was executed in 55 sec 🙁

  but one thing more I would like to ask, Is multiple indexes on same table(orderlines) will effect the performance of my complete application,where this table is used?

  I already have some indexes on orderlines table.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 18, 2011 at 2:01 am

  #1395730

  There is of course a cost associated with maintaining an index - another subject.

  Here's a few changes. Check out how long it takes to run, then I guess it's the final stage - put it all back together again.

  I notice you put an index hint in there again - don't do it! It seems that it's a habit you have acquired. Lose the habit. It's extremely unlikely that you will improve the performance of a query with it, and highly likely that you will make it worse. In virtually every case, SQL Server will choose the best indexes for the job.

  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

  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

  October 18, 2011 at 2:39 am

  #1395738

  Thx for ur advice, regarding force indexing :-),now i m not using it.

  Now query is taking 58 sec to execute,find the attached paln and qry as well

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 18, 2011 at 3:01 am

  #1395742

  The plan for the orderlines query looks fine, however there's a third query shown:

  insert [#orderlines_________________________________________________________________________________________________________000000000118] select * from [#orderlines_________________________________________________________________________________________________________000000000118]

  I don't know where this has come from and you don't need it. Also, since you can extract the query from the plan, you don't need to post the query separately. Just the plan is fine.

  Now, with the third query removed from your batch, how long does the batch take to run? I'd expect about 10 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