Query performance

Question

Query performance

jseashore-85696

SSC-Addicted

Points: 444
More actions
March 6, 2006 at 2:54 pm

#90622

I have a question concerning a SQL query. I have a query whose inner query returns a very small number of records, say under 500. Then, in the outer query I join it with a number of tables. This query takes a very long time to execute (like 40 seconds or more). When I execute the inner query, it only takes a couple seconds to execute. The joins in the outer, in theory, shouldn’t take too long since they act only on a limited number of records. But, they seem to be making a very large difference. When testing the query, each join I include in the outer query increases the execution time by several seconds. So it appears to me the query optimizer isn’t doing the inner query first, then joining those limited number of rows in the outer. Any ideas on how I can fix this?
TSQL below

SELECT *
FROM (
    SELECT
    QP1.CreditUnionID,
    QP1.QuoteNumber,
    QU1.QuoteID,
    QU1.QuotePackageID,
    QU1.QuotingBranchID,
    QU1.ProductID,
    QU1.QuotingUserID,
    QU1.DateQuoted,
    QU1.DateExpires,
    QU1.TotalTaxes,
    QU1.TotalPremium,
    QU1.TotalBuyerDiscount,
    QU1.TotalBuyerCost,
    QU1.TotalCUCompensation,
    QU1.TotalASCompensation,
    QU1.TotalUWCompensation,
    QU1.TotalVICompensation,
    QU1.TotalDueAllied,
    QU1.QuoterIncentive,
    QU1.IssuerIncentive,
    QU1.TotalCreditGivenByAS,
    QU1.TotalCreditGivenByCU,
    QU1.TotalBuyerCredit
    FROM Quotes QU1
    JOIN Applications AP1 ON AP1.QuoteID = QU1.QuoteID
    JOIN QuotePackages QP1 ON QP1.QuotePackageID = QU1.QuotePackageID
    JOIN ObjectStatus AS1 ON AS1.ObjectID = QU1.QuoteID AND AS1.EventCategoryID = 1
    JOIN ObjectStatus PS1 ON PS1.ObjectID = QU1.QuoteID AND PS1.EventCategoryID = 2
    WHERE QU1.IsRemoved = 0 AND QP1.CreditUnionID = 292 AND QU1.ProductID = 2 AND AS1.EventID IN (2,1,5) AND PS1.EventID IN (0,12)) FD
INNER JOIN JOIN Applications AP2 ON AP2.QuoteID = FD.QuoteID
INNER JOIN JOIN CreditUnions CU2 ON CU2.CreditUnionID = FD.CreditUnionID
INNER JOIN JOIN QuoteMemberInfo MI2 ON MI2.QuotePackageID = FD.QuotePackageID
INNER JOIN JOIN Users US1 ON US1.UserID = FD.QuotingUserID
INNER JOIN JOIN Users US2 ON US2.UserID = AP2.IssuingUserID
INNER JOIN JOIN Products PR2 ON PR2.ProductID = FD.ProductID
INNER JOIN ObjectStatus AS2 ON AS2.ObjectID = FD.QuoteID AND AS2.EventCategoryID = 1
INNER JOIN Events AE2 ON AE2.EventID = AS2.EventID
INNER JOIN ObjectStatus MS2 ON MS2.ObjectID = FD.QuotePackageID AND MS2.EventCategoryID = 8
INNER JOIN Events ME2 ON ME2.EventID = MS2.EventID
INNER JOIN ObjectStatus VS2 ON VS2.ObjectID = FD.QuoteID AND VS2.EventCategoryID = 21
INNER JOIN Events VE2 ON VE2.EventID = VS2.EventID

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kory Becker Hall of Fame Points: 3585 More actions · Answer 1

Do you need all columns from all thirteen tables returned. This could be potentialy an enormous amount of data if VarChar(8000), text, ntext and images are present.

I would start by limiting the columns returned to what is needed then we can go through the query plan!

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

Yep, I agree with Kory...

Also, I believe that moving the reference to constants out of the ON clauses and into a WHERE clause will help a bit...

The other thing may be that you may have some joins that return more than 1 row for a given condtion which will cause many more rows than what you're looking for to be returned... not quite as bad as a CrossJoin but almost.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 3

What about indexes on joined fields?

Look on Execution Plan for this query.

_____________
Code for TallyGenerator

Kory Becker Hall of Fame Points: 3585 More actions · Answer 4

Ok, set statistic IO on and showplan and post the output. After that you can specify the columns needed and then re-run the SQL with statistic IO on and we can see how many less pages are read.

jseashore-85696 SSC-Addicted Points: 444 More actions · Answer 5

I'm working on pairing down the number of columns returned. Good call... there was a ridiculous number of them.

I was thinking the same thing regarding the indexes... I took a peak at the execution plan... no table scans.

I was hoping to throw an index or two down and be done with it.

I'm not used to performance tweaking queries of such magnitude, so I appreciate the comments that you all do have.

Here is the statistic IO info:

Table 'Applications'. Scan count 2, logical reads 3422, physical reads 1, read-ahead reads 3251.

Table 'ObjectStatus'. Scan count 8, logical reads 5899, physical reads 1, read-ahead reads 5011.

Table 'Quotes'. Scan count 1, logical reads 11150, physical reads 32, read-ahead reads 10714.

Table 'QuoteMemberInfo'. Scan count 1, logical reads 11043, physical reads 29, read-ahead reads 11007.

Table 'QuotePackages'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 10.

Table 'Users'. Scan count 2, logical reads 619, physical reads 0, read-ahead reads 0.

Table 'Events'. Scan count 3, logical reads 12, physical reads 0, read-ahead reads 0.

Table 'CreditUnions'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

Table 'Products'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

The SHOWPLAN_ALL is pretty nasty... ar Is there a certain few columns that would help? I've been trying to get the result set put in there and it's just a monster...