Click here to Skip to main content
15,076,465 members
Please Sign up or sign in to vote.
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
Updated 14-Mar-13 20:24pm
v3
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

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'
   
v2
Comments
Maciej Los 15-May-13 8:07am
   
Please, use a tools (code menu) to format text!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900