October 17, 2011 at 6:31 am
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.
October 17, 2011 at 6:43 am
Comment out the index hint!
FROM orderlines ol -- with (index(Ix_Prodline)) <<-- keep this out of the query!!
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
October 17, 2011 at 6:47 am
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
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
October 17, 2011 at 6:49 am
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.
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
October 17, 2011 at 7:32 am
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
October 17, 2011 at 7:37 am
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.
October 17, 2011 at 7:41 am
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.
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
October 17, 2011 at 7:50 am
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.
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
October 17, 2011 at 7:52 am
I'd be most happy to be proven wrong... 😉
October 17, 2011 at 7:55 am
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...
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
October 17, 2011 at 8:00 am
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?
October 18, 2011 at 12:28 am
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.
October 18, 2011 at 2:01 am
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 #####
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
October 18, 2011 at 2:39 am
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
October 18, 2011 at 3:01 am
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.
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 - 16 through 30 (of 110 total)
You must be logged in to reply to this topic. Login to reply