how to tune this query

Question

how to tune this query

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 1

For the most part, I agree... especially if you check out the I/O and all.

I was just reminiscing about when I first got to my "new" job 4 years ago... many of the query's were taking 8 hours just to process 10 or 20 thousand rows... one of the geniuses that wrote those figured out a way to do one of those in about 22 minutes. Everyone thought things couldn't get any better... I didn't know how long it originally took and, like the bumble bee who doesn't know it's not supposed to be able to fly, I rewrote it... it runs in 6 seconds now 😉

My only point was that somewhere along the line, ya gotta look at things. But, I do agree that a 15 column covering index is probably not the way to do it :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 2

P1naga...

Just for grins and because of the Left Outer Join to t1, try changing your from clause from this...

FROM

dbo.S_PRILSTITEM T1

INNER JOIN dbo.SPRILST T2 ON T1.PRI_D = T2.ROWD

LEFT OUTER JOIN dbo.SVOLDISCNT T3 ON T1.VOL_DISID = T3.ROWD

WHERE

(T1.PRI_D = '5-4-330')

... to this...

FROM

dbo.S_PRILSTITEM T1

INNER JOIN dbo.SPRILST T2 ON T1.PRI_D = T2.ROWD

AND T1.PRI_D = '5-4-330'

LEFT OUTER JOIN dbo.SVOLDISCNT T3 ON T1.VOL_DISID = T3.ROWD

See if you get the same result, only faster...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jeff Moden (12/19/2007)
I didn't know how long it originally took and, like the bumble bee who doesn't know it's not supposed to be able to fly, I rewrote it... it runs in 6 seconds now 😉

Hehehe. Nice one. What did the geniuses say?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 4

GilaMonster (12/19/2007)
Jeff Moden (12/19/2007)
I didn't know how long it originally took and, like the bumble bee who doesn't know it's not supposed to be able to fly, I rewrote it... it runs in 6 seconds now 😉
Hehehe. Nice one. What did the geniuses say?

Probably "That can't possibly be right, put it back the way it was." 😛

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 5

DonaldW (12/20/2007)
GilaMonster (12/19/2007)
Jeff Moden (12/19/2007)
I didn't know how long it originally took and, like the bumble bee who doesn't know it's not supposed to be able to fly, I rewrote it... it runs in 6 seconds now 😉
Hehehe. Nice one. What did the geniuses say?
Probably "That can't possibly be right, put it back the way it was." 😛

Heh... Spot on, Donald! Took me a bit of time to convice them that it actually did the same thing as their code and took them a lot of time to test it to make sure. Most of "them" were Oracle programmers that had no clue what a Temp table was never mind how to use it as an interim-answer "scratchpad". They did everything with a cursor across 23 stored procedures 8 levels deep. Talk about "RBAR on steroids"...

My greatest ally (and very good friend, too) turned out to be the Production DBA... He'd been trying to get folks to "think set-based" and this was the kind of proof he needed.

But I digress... my point is that sometimes you've just gotta try and value your mistakes and failures. Heh... you think Ivory soap, 3M sticky notes, or Teflon was invented on purpose? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 6

Jeff Moden (12/20/2007)
DonaldW (12/20/2007)
GilaMonster (12/19/2007)
Jeff Moden (12/19/2007)
I didn't know how long it originally took and, like the bumble bee who doesn't know it's not supposed to be able to fly, I rewrote it... it runs in 6 seconds now 😉
Hehehe. Nice one. What did the geniuses say?
Probably "That can't possibly be right, put it back the way it was." 😛
Heh... Spot on, Donald! ...

Paul Nielson told a similar story last year at PASS. He reworked a process that took hours and the finished product took minutes or even seconds. The customer refused to implement it because if it was that much faster it couldn't possibly be producing correct results. They stuck to that even when they could validate the results.