Fine tune query with Left Outer Joins

Question

0.00/5 (No votes)

See more:

Hi All,
I am asked to fine tune a query which taking forever to execute. :( .. It uses 3 huge tables, with 12 lakhs data each and the query contanins many left outer joins which I think result in a huge cross join.

SQL

SELECT ss.[ID],   
 ss.[Code],  
 ss.[center],  
 dupCheck.[ID],  
 hdr.ObjectKey,
 hdr.[Status],
 map.ID
FROM snapshot ss  
LEFT OUTER JOIN header hdr ON hdr.Code = ss.center  
LEFT OUTER JOIN Mapping map ON ss.Code = map.Code   
LEFT OUTER JOIN 
	(SELECT MIN(id) AS [ID] FROM snapshot WHERE status='P' GROUP BY Code, Filename) dupCheck ON dupCheck.[ID] = ss.ID  
WHERE ss.status='P'

Please give me some ideas to fine tune.. Will UNION ALL with left join help?

Thanks in advance,

Posted 14-Mar-13 19:37pm

SruthiR

Updated 14-Mar-13 20:24pm

v3

Add a Solution

Comments

Prasad Khandekar 15-Mar-13 2:33am

Hello Sruthi,
Although I not a DBA/SQL Expert, In my opnion as long as there are a indexs on header.code & mapping.code those joins should not slow down the query. Although I think the second lo join should be writtern as "EFT OUTER JOIN Mapping map ON map.Code = ss.Code". I suspect that third lo is the one which is causing the problem.
Is that join really required?

Regards,

SruthiR 15-Mar-13 2:48am

Yes. There are indexes in both the tables. I think the third join is required for their logic which we are completely unaware of.. :( think its tough...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vipin kumar.P · Answer 1 · 2013-05-15T01:24:00

have a look at following Query

SQL

SELECT MIN(id) AS [ID]
INTO #dupCheck
FROM snapshot WHERE status='P' GROUP BY Code, Filename

SELECT ss.[ID],   
 ss.[Code],  
 ss.[center],  
 dupCheck.[ID],  
 hdr.ObjectKey,
 hdr.[Status],
 map.ID
FROM snapshot ss  
LEFT OUTER JOIN header		hdr ON hdr.Code = ss.center  
LEFT OUTER JOIN Mapping		map ON ss.Code = map.Code   
LEFT OUTER JOIN #dupCheck	dc	ON	dc.[ID] = ss.ID  
WHERE ss.status='P'