查看以下查询

  SELECT  MIN（id） AS  [ID] 
 INTO  #dupCheck 
 FROM  snapshot  WHERE 状态= '  P'  GROUP   BY 代码，文件名

 SELECT  ss。[ID]，
 ss。 [代码]，
 ss。[中心]，
 dupCheck。[ID]，
 hdr.ObjectKey，
 hdr。[状态]，
 map.ID 
 FROM  snapshot ss 
 LEFT   OUTER   JOIN  header hdr  ON  hdr.Code = ss.center 
 LEFT   OUTER   JOIN 映射地图 ON  ss.Code = map.Code 
 LEFT   OUTER   JOIN  #dupCheck dc  ON  dc。[ID] = ss.ID 
 WHERE  ss.status = '  P' 

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.

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,

have a look at following Query

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'