Baffling performance issue

Question

Baffling performance issue

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

Is it actually your goal to return over 4 million rows?

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Jeff Moden (12/21/2009)
Is it actually your goal to return over 4 million rows?

Jeff, haven't you learned by now that some questions just shouldn't be asked?? :w00t: 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 3

Robert... you have 30 or so joins in your query and the end result according to the Execution Plan you attached is over 4 million rows. First, I don't believe that a 4 million row return was intended and likely means that you have at least one unintentional partial cross join in the form of a many-to-many relationship or improper criteria somewhere along the line.

Second, while you could probably ferret out the problems with this monster over time, it would be far better if you divided this problem up and stored a couple of interim results in smaller temp tables. That will do two things.... first, SQL won't have to hold 30 joins over it's head while it's trying to run and second, it'll make it much easier to find the partial cross joins I spoke of.

Break this baby up! Divide'n'conquer!

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

Jeff Moden (12/21/2009)
Is it actually your goal to return over 4 million rows?

I don't think he is. Look at the arrow leading towards the Compute Scalar (2nd last operator). Estimated rows 4.1 million. Actual rows 19.

It's odd, right up to that last loop join the estimates are reasonable (147 vs actual 19 and 1 vs 4) It's that last nested loop join where the estimate just jumps to the 4 million. I don't see why that should have happened.

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

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

Couple things...

Do you know that the Statement_History table has no clustered index?

Consider a nonclustered index on the Accounts table on the Account_Type_ID column. There's a clustered index (read table) scan on that table that returning only 96 rows. It may be selective enough for just the NC index to be useful

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