How this query could be improved?

Question

How this query could be improved?

virgilrucsandescu

SSCrazy

Points: 2457
More actions
October 16, 2007 at 12:30 pm

#62967

The query & the execution plan are in the attached image - it takes ~ 2 mins to return 3 rows. the underlined table is that one responsible for 80% of the time. If anybody has any idea, please help!

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What jumps out at me to start are the outer joins. Are these really necessary?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323518 More actions · Answer 2

can you paste the query as text here as well? it helps to be able to see that as text and not as an image in order to help better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 3

SELECT

ApplicantContactGrant.CompanyID, ApplicantContactGrant.ApplicantID, ApplicantContactGrant.GrantName,

ApplicantContactGrant.DtAdded, ApplicantContactGrant.StatusCD,

Applicant.OrgName,

ApplicantContactGrantDetails.Amount, ApplicantContactGrantDetails.IK2TotalAmount,

ApplicantContactGrantInKindP2.IKValue, ApplicantContactGrantInKindP2.IKDate,

ValidCurrency.Name

FROM ApplicantContactGrant

INNER JOIN Applicant

ON ApplicantContactGrant.CompanyID=Applicant.CompanyID AND ApplicantContactGrant.ClientID=Applicant.ClientID AND

ApplicantContactGrant.ApplicantID=Applicant.ApplicantID

LEFT OUTER JOIN ApplicantContactGrantDetails

ON ApplicantContactGrant.CompanyID=ApplicantContactGrantDetails.CompanyID AND

ApplicantContactGrant.ClientID=ApplicantContactGrantDetails.ClientID AND

ApplicantContactGrant.ApplicantID=ApplicantContactGrantDetails.ApplicantID AND

ApplicantContactGrant.ContactID=ApplicantContactGrantDetails.ContactID AND

ApplicantContactGrant.GrantID=ApplicantContactGrantDetails.GrantID

LEFT OUTER JOIN ApplicantContactGrantInKindP2

ON ApplicantContactGrantDetails.CompanyID=ApplicantContactGrantInKindP2.CompanyID AND

ApplicantContactGrantDetails.ClientID=ApplicantContactGrantInKindP2.ClientID AND

ApplicantContactGrantDetails.ApplicantID=ApplicantContactGrantInKindP2.ApplicantID

LEFT OUTER JOIN ValidCurrency

ON ApplicantContactGrantInKindP2.CompanyID=ValidCurrency.CompanyID AND

ApplicantContactGrantInKindP2.ClientID=ValidCurrency.ClientID AND

ApplicantContactGrantInKindP2.IKCurrencyID=ValidCurrency.CurrencyID

WHERE ValidCurrency.Name='AH - Comp Animal E' AND

ApplicantContactGrant.CompanyID=4 AND ApplicantContactGrant.StatusCD='A'

ORDER BY ApplicantContactGrant.CompanyID

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

What indexes do you have going on each table? You'd need several compound indexes to make this work best?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Hello virgilash,

the first thing I would do is replace all LEFT JOINs with INNER JOINs.

Since you filter for ValidCurrency.Name in the WHERE clause there should be no outer rows.

Best Regards,

Chris Büttner