Query Optimization using Indexes

Question

Post reply

Query Optimization using Indexes

Viewing 15 posts - 31 through 45 (of 110 total)

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

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

Hello adlakha.22,

there are some things I don't understand seeing your query and your data structure, let me explain:

You manage two tables, orders and orderlines. I suppose there are a hierarchical relation between them.

Orders: primary key and cluster is

[cono] ASC, [orderno] ASC, [ordersuf] ASC, [oetype] ASC

Orderlines: primary key and cluster is

[cono] ASC, [orderno] ASC, [ordersuf] ASC, [linenum] ASC

These primary keys confounds me. Is [oetype] needed to identify an order?, if so, why isn't it in the Orderlines PK?, if not, why is it in the Orders PK?.

Next, in your query, your JOIN condition is another:

... from orderlines

inner join orders as o

on orderlines.ordernumber =o.ordernumber

I suppose ordernumber is a redundant key. But you can join both tables trough the primary keys, and this way maybe you can improve the execution plan.

So I will suggest you to:

- clarify what is the role of the [oetype] field,

- join the tables using the primary keys

and test it if improves performance.

Regards,

Francesc

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

frfernan (10/18/2011)
... But you can join both tables trough the primary keys...

Can you please show what you mean?

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

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

ChrisM@Work (10/18/2011)
frfernan (10/18/2011)
... But you can join both tables trough the primary keys...
Can you please show what you mean?

Yes of course, seeing the table definitions I suppose that the "natural way" to join both tables is

... from orderlines

inner join orders as o

on orderlines.cono = orders.cono

AND orderlines.orderno = orders.orderno

AND orderlines.ordersuf = orders.ordersuf

and I am in doubt about the role of [oetype], if it should, or not, be included in the above code because this column belongs to one PK but not to both PK.

I suggest it supposing that we can link both tables using the primary keys but maybe it is false, the original query uses other fields (ordernumber) to link these tables.

Francesc

Mishal Ten Centuries Points: 1103 More actions · Answer 4

@frfernan

Ordernumber is also unique key in both the tables,

and i have also try to join the qry on the basees of cono,ordersuf,orderno that are the primary keys,

but still it is taking 65 secnds to execute..

that why we r trying to optimize the SubQuery(select ___from orderlines where....)

i think the most of the time is consumed by it.

Mishal Ten Centuries Points: 1103 More actions · Answer 5

Hi Chris

could u plz tell me wht do u mean by

"Now, with the third query removed from your batch, how long does the batch take to run? I'd expect about 10 seconds. "

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

adlakha.22 (10/18/2011)
@frfernan
Ordernumber is also unique key in both the tables,
and i have also try to join the qry on the basees of cono,ordersuf,orderno that are the primary keys,
but still it is taking 65 secnds to execute..
that why we r trying to optimize the SubQuery(select ___from orderlines where....)
i think the most of the time is consumed by it.

Hello,

I will follow with the idea of using the PK, it is also cluster, so it should help to improve performance.

Now I will suggest you to use the subquery showed by Chris but don't group by ordernumber but by cono, ordersuf, orderno, this way that subquery will group using the cluster. And next yo can try to join the orders table with the subquery using not ordernumber but the PK fields.

Good luck,

Francesc

Mishal Ten Centuries Points: 1103 More actions · Answer 7

@frfernan

Here it is Execution Plan of Query using Primary Keys in group By and Order By Clause...nd now it is executed in 54 Sec

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

gofrancesc

Hall of Fame

Points: 3269

October 18, 2011 at 5:29 am

#1395824

Oh, now I see you did it...

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

adlakha.22 (10/18/2011)
Hi Chris
could u plz tell me wht do u mean by
"Now, with the third query removed from your batch, how long does the batch take to run? I'd expect about 10 seconds. "

When I look at the plan, I see Query 1, Query 2 and Query 3.

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

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

Mishal Ten Centuries Points: 1103 More actions · Answer 11

Mishal

Ten Centuries

Points: 1103

October 18, 2011 at 6:11 am

#1395846

@ninja's_RGR'us

Wht u suggest to do?

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

adlakha.22 (10/18/2011)
@Ninja's_RGR'us
Wht u suggest to do?

I already gave my opinion.

I love perf tuning, but 30 hours of work for free is outside my charity range at the moment. 😉

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

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

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

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

Couldn't help it Remi. BTW the monster you put up on the watercooler - wanna hand with it? :hehe:

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

ChrisM@Work (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
Couldn't help it Remi. BTW the monster you put up on the watercooler - wanna hand with it? :hehe:

The 40K to 1 missed estimation?

Nah, I just moved the where correlation deeper in the outer apply and the whole report is well under subsecond now ;-). Instead of 4-5 minutes! :w00t: