Query Optimization using Indexes

Question

Query Optimization using Indexes

Viewing 15 posts - 46 through 60 (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

frfernan (10/18/2011)
Ninja's_RGR'us (10/18/2011)
Just a reminder of the goal of the OP. You guys were not supposed to play with the qry at all! 😀
Query Optimization using Indexes
Ha ha, good reminder!.
But this goal is too strict, isn't?. In real world, when I need to optimize a query, I never think in so strict way; must I add indexes?, or maybe redefine indexes, or reorganize them, or add hints, or even recode my query?. Or add RAM, or change server, or search for another job?, there is a lot of options to try. Anyway the goal is explicit, "using indexes".
About my prior post please forget it, I throw it mistakenly.
And about the original query:
I think about some changes (oops, sorry, but play with the query is so tempting... 🙂 ). First, leave all casts to the most outer query freeing the subquery to do these casts. The query should look like
WITH prodline as (
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'
)
, LinesTotals AS (
SELECT
cono,
orderno,
ordersuf,
[Order#] = ordernumber,
[Cust#] = MAX(custno),
[OE Type] = MAX(oetype),
[Trans] = MAX(transtype),
[Whse] = MAX(whse),
[Ship To ] = MAX(shipto),
[Qty Ordered] = sum(qtyord),
[Qty Shipped] = sum(qtyshp),
[Line Amount] = sum(lineamt),
[Ordered Amt] = sum(ordamt),
[Shipped Amt] = sum(shpamt),
[Avg Cost] = sum(avgcost),
[STD Cost] = sum(stdcost),
[Addon Cost] = sum(addcost),
[Lnd Cost] = sum(landcost),
[Enter Date] = MAX(enterdt),
[Oper ID] = MAX(operid),
[SalesRep In] = MAX(slsrepin),
[SalesRep Out] = MAX(slsrepout),
[TakenBy] = MAX(takenby),
[ProdLine] = MAX(ol.prodline)
FROM orderlines ol
INNER JOIN prodline pl ON pl.prodline = ol.prodline
WHERE 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')
and whse IN ('100','101','102','103','104','105','106','107','108','120','121','122','151')
GROUP BY ordernumber, ol.prodline, enterdt, operid,slsrepin
)
SELECT
[Customer Name] = o.custname,
[Stage] = o.stagedesc,
[Ship To Name] = o.shiptonm,
--[Inv Amt] = cast(sum(o.totinvamt) as decimal(19,2)),
[Req Ship Date] = convert(varchar, o.reqshipdt , 101),
[Promise Date] = isnull(convert(varchar, o.promisedt, 101),''),
[State] = o.[state],
[City] = o.city,
[Zipcode] = o.zipcode ,
ol.[Order#],
ol.[Cust#],
oetype = DBO.GETOETYPE(ol.[OE Type]),
ol.[Trans],
ol.[Whse],
[Ship To ] = isnull(convert(varchar,ol.[Ship To ],101),''),
ol.[Qty Ordered],
ol.[Qty Shipped],
[Line Amount] = cast(ol.[Line Amount] as decimal(19,2)),
[Ordered Amount] = cast(ol.[Ordered Amount] as decimal(19,2)),
[Shipped Amount] = cast(ol.[Shipped Amount] as decimal(19,2)),
[Avg Cost] = cast(ol.[Avg Cost] as money),
[STD Cost] = cast(ol.[STD Cost] as money),
[Addon Cost] = cast(ol.[Addon Cost] as money),
[Lnd Cost] = cast(ol.[Lnd Cost] as money),
[Profit] = cast((ol.[Line Amount] - ol.[Lnd Cost]) as decimal(19,2)),
[Profit%] = cast (
(select case
when ol.[Line Amount] = 0 and ol.[Lnd Cost] <> 0 then -100
when ol.[Lnd Cost] = 0 then NULL
else (1 - ol.[Lnd Cost]/ol.[Line Amount]) * 100 end)
as decimal(19,2)),
[Enter Date] = convert(varchar, ol.[Enter Date], 101),
ol.[Oper ID],
ol.[SalesRep In],
ol.[SalesRep Out],
ol.[TakenBy],
ol.[ProdLine]
FROM orders o
INNER JOIN LinesTotals ol ON ol.cono = o.cono AND ol.orderno = o.orderno AND ol.ordersuf = o.ordersuf
Actually I don't rely in any big improvement, but world is full of surprises...
Second, what's the cost of using the function DBO.GETOETYPE?. It is a candidate to punish performance, try to remove it and see the results.
Francesc

We're likely to confuse the OP here, or worse still put him/her off altogether, if we force him to sing from too many sheets at the same time.

My plan was to join the orders table to the temp table result of the orderlines aggregate, joining on only the necessary columns and applying any other restrictions / aggregates.

The function is one entity to cover but 30s to return 130k rows aggregated from 750k isn't too shabby - I'm looking for 10s.

I'd like to know what the relationships are between the columns which are named ordersomething

Next question - what happens to 130k rows? It's a hell of a lot of output.

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

@ninja's_RGR'us

ya...u was right.."world is full of surprises..."

like i again I got the surprise of 58 sec execution time:-D

and also seems result is not correct

u can see it from Execution Plan

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

adlakha.22 (10/18/2011)
@Ninja's_RGR'us
ya...u was right.."world is full of surprises..."
like i again I got the surprise of 58 sec execution time:-D
and also seems result is not correct
u can see it from Execution Plan

How can I possibly see that the results are wrong from the exec plan alone? 😉

R u on a test server?

If so, can you build the index I asked about 2 days ago and see what gain it gets you and also the size of the new index?

Mishal Ten Centuries Points: 1103 More actions · Answer 4

@ninja

Are u talking Abt this one

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

I did't got any thing else, regarding index from ur previous posts!

Means Include Index

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

adlakha.22 (10/18/2011)
@Ninja
Are u talking Abt this one
"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."
I did't got any thing else, regarding index from ur previous posts!
Means Include Index

Open up the plan in SSMS 2008+ or sql sentry's free plan explorer (latest & fastest version).

There will be a script to build the index there (not available in ssms 2005 even if it's in the xml of the plan).

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

gofrancesc Hall of Fame Points: 3269 More actions · Answer 6

ChrisM@Work (10/18/2011)
...We're likely to confuse the OP here...

Hello, I agree. Let's me simply put next my ideas about this problem:

- I suppose [ordernumber] is a redundant identifier, I think about take off this column and replacing it by [cono], [orderno], [ordersuf] that is the clustering key.

- Moving all casts and other functions to its proper place could help a bit.

- The function DBO.GETOETYPE could be a major performance problem.

adlakha.22 (10/18/2011)
...I got the surprise of 58 sec execution time...

Seeing your execution plan I see my suggested query was incorrect and you corrected it,

...LinesTotals AS (

SELECT

cono,

orderno,

ordersuf,

[Order#] = ordernumber,

FROM orderlines ol

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

WHERE ...

GROUP BY ordernumber...

applying MAX to cono, orderno, ordersuf; as I just commented above my idea is to take off ordernumber, so I would prefer apply MAX to ordernumber and group by cono, orderno, ordersuf. That is, focus on the clustering keys.

If using the clustering keys doesn't help, if DBO.GETOETYPE doesn't punish performance, I'm going out of ideas...

Regards,

Francesc

Mishal Ten Centuries Points: 1103 More actions · Answer 7

@frfernan

as u say "so I would prefer apply MAX to ordernumber and group by cono, orderno, ordersuf. That is, focus on the clustering keys."

I again do some changes in Query,i.e group by (cono,ordersuf.oredrno) and Max to ordernumber.

But still it is taking 73 sec to execute.. 🙁

Here u can see the execution plan.

gofrancesc Hall of Fame Points: 3269 More actions · Answer 8

Good morning,

have you tried to run your query without the column "DBO.GETOETYPE(orderlines.oetype)"?, to see the impact of this function in the overall performance.

Francesc

Mishal Ten Centuries Points: 1103 More actions · Answer 9

Good Morning frfernan 🙂

Now I have removed that GETOETYPE function Call From My Query,

--------------------------------------------------------------------

ol.[Cust#],

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

ol.[Trans],

ol.[Whse],

--------------------------------------------------------------------

But did't found any improvement in Exceution Time...Its still 65 secs...

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

adlakha.22 (10/19/2011)
@frfernan
as u say "so I would prefer apply MAX to ordernumber and group by cono, orderno, ordersuf. That is, focus on the clustering keys."
I again do some changes in Query,i.e group by (cono,ordersuf.oredrno) and Max to ordernumber.
But still it is taking 73 sec to execute.. 🙁
Here u can see the execution plan.

You've changed the query quite a bit - the temp table is now a CTE, as is the orderlines query. The actual plan attached to your message is truncated too, unfortunately.

I'd have been surprised if writing these queries as CTE's had improved performance, and they haven't. It's good to experiment though, that's how you will find out what works and what doesn't.

Are you able to elaborate on the Query 3 from yesterday?

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

Good morning

@chris-2 i am not able to get that Query3 ,

Every Morning I have a hope with u guys...Expecting some logical magic.

That improve my Query performance and my application as well.

if u have something to suggest then plz post it and help me out of this query hell.

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

adlakha.22 (10/20/2011)
Good morning
@chris-2 i am not able to get that Query3 ,
Every Morning I have a hope with u guys...Expecting some logical magic.
That improve my Query performance and my application as well.
if u have something to suggest then plz post it and help me out of this query hell.

Thank you for your patience and perseverence, adlakha.22. I'd like to backtrack to the query from orderlines which gave the best performance so far, 30s IIRC. Here it is again, can you please run it exactly as it is and post the actual plan. In the meantime I'll be working on a couple of queries to try to determine a "baseline" performance, i.e. the best performance you are likely to get.

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

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

Here are three test queries which will give an idea of baseline performance. Query 3 takes 10seconds to run against a similar table of 40 million rows here. Please change the datatype of the variable ordernumber to match the column, then run each query separately and record the time, finally run all three in a batch and capture & post the actual execution plan. Thanks.

-- 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 14

Thanks for Reply Chris

I again Executed the Query , nd did't made any changes except column names to match them with group By Clause,

U can see Execution Plan attached.

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

adlakha.22 (10/20/2011)
Thanks for Reply Chris
I again Executed the Query , nd did't made any changes except column names to match them with group By Clause,
U can see Execution Plan attached.

How long did this take to run?

What are you using to run your queries? Are you simply pasting them into a SSMS query window or do you have an alternative client?

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