使用左外部联接进行微调查询 [英] Fine tune query with Left Outer Joins

问题描述

大家好，

我被要求微调一个永远执行的查询。 :(它使用3个巨大的表，每个数据包含12万个数据，查询包含许多左外连接，我认为这会产生巨大的交叉连接。



< pre lang =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 映射地图 ON ss.Code = map.Code
LEFT OUTER JOIN
（ SELECT MIN（id） AS [ID] FROM snapshot WHERE status = ' P' GROUP BY 代码，文件名）dupCheck ON dupCheck。[ID] = ss.ID
WHERE ss.status = ' P'

请给我一些微调的想法.. UNION ALL会有左加入帮助吗？



提前致谢，

解决方案

查看以下查询

  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'

这篇关于使用左外部联接进行微调查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！