SQL优化实例：从运行30分钟到运行只要30秒

以下的SQL语句在服务器需要运行长达30分钟才能完成:

SELECT      dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode, 
                      dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity  *  dbo.Commodity.ConvertRate  AS  Quantity,  CONVERT ( datetime ,  DATENAME (yyyy, 
                      dbo.ComFlow.FlowDate)  +   ' - '   +   DATENAME (mm, dbo.ComFlow.FlowDate)  +   ' - '   +   DATENAME (dd, dbo.ComFlow.FlowDate))  AS  FlowDate, 
                      dbo.ComFlow.SalType, dbo.Employee.DepartCode  AS  DepartIn, dbo.Sale.DepartCode  AS  DepartOut, 
                      dbo.ComFlow.Quantity  *  dbo.Commodity.TradePrice  *  dbo.Commodity.Discount  /   100   AS  Total, dbo.Department.GrpCode  AS  GrpCodeIn, 
                      Department1.GrpCode  AS  GrpCodeOut
FROM          dbo.ComFlow  INNER   JOIN
                      dbo.Customer  ON  
                dbo.ComFlow.SalType  IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode =  dbo.Customer.CustCode 
                Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退货') AND ComFlow_1.OutCustCode =  Customer_1.CustCode
             INNER   JOIN
                      dbo.CustomerRelation  ON  dbo.ComFlow.ComCode  =  dbo.CustomerRelation.ComCode  AND  
                      dbo.CustomerRelation.CustCode  =  dbo.Customer.CustCode  INNER   JOIN
                      dbo.Employee  ON  dbo.CustomerRelation.EmpCode  =  dbo.Employee.EmpCode  INNER   JOIN
                      dbo.Sale  ON  dbo.ComFlow.SaleCode  =  dbo.Sale.SaleCode  INNER   JOIN
                      dbo.Department  ON  dbo.Department.DepartCode  =  dbo.Employee.DepartCode  INNER   JOIN
                      dbo.Department  AS  Department1  ON  Department1.DepartCode  =  dbo.Sale.DepartCode  AND  
                      dbo.Department.GrpCode  <>  Department1.GrpCode  INNER   JOIN
                      dbo.Commodity  ON  dbo.ComFlow.ComCode  =  dbo.Commodity.ComCode
WHERE      ( NOT  (dbo.ComFlow.SalType  =  N ' 流向退货 ' ))  OR
                      ( NOT  (dbo.Customer.Type  =  N ' 医药公司 ' ))

虽然说，我们使用这个语句的应用是一个BI应用，实时性要求不高，但是，我觉得没有道理会运行这么久，应该有办法优化。

第一步，我看了看索引，好像没有问题，都有
第二步，检查关系，有没有错，没有错，和应用要求是一致的，尤其计算出来的结果和同事使用另外一种方法的计算结果是一致的（同事使用多个视图分步累加）。
第三步，看看这个语句有没有什么特别之处？
      我注意到特别之处就是使用Pink底色标出的部分：
dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode =  dbo.Customer.CustCode                
 Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退货') AND ComFlow_1.OutCustCode =  Customer_1.CustCode

这是一个Or关系的关联？就是这个问题？
分析这个语句可以看出，这个Or语句其实是可以分解成Union语句的，所以把它变成下面的：

SELECT      dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode, 
                      dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity  *  dbo.Commodity.ConvertRate  AS  Quantity,  CONVERT ( datetime ,  DATENAME (yyyy, 
                      dbo.ComFlow.FlowDate)  +   ' - '   +   DATENAME (mm, dbo.ComFlow.FlowDate)  +   ' - '   +   DATENAME (dd, dbo.ComFlow.FlowDate))  AS  FlowDate, 
                      dbo.ComFlow.SalType, dbo.Employee.DepartCode  AS  DepartIn, dbo.Sale.DepartCode  AS  DepartOut, 
                      dbo.ComFlow.Quantity  *  dbo.Commodity.TradePrice  *  dbo.Commodity.Discount  /   100   AS  Total, dbo.Department.GrpCode  AS  GrpCodeIn, 
                      Department1.GrpCode  AS  GrpCodeOut
FROM          dbo.ComFlow  INNER   JOIN
                      dbo.Customer  ON  dbo.ComFlow.SalType  IN  (N ' 促销 ' , N ' 流向退货 ' , N ' 多级流向 ' )  AND  dbo.ComFlow.CustCode  =  dbo.Customer.CustCode  INNER   JOIN
                      dbo.CustomerRelation  ON  dbo.ComFlow.ComCode  =  dbo.CustomerRelation.ComCode  AND  
                      dbo.CustomerRelation.CustCode  =  dbo.Customer.CustCode  INNER   JOIN
                      dbo.Employee  ON  dbo.CustomerRelation.EmpCode  =  dbo.Employee.EmpCode  INNER   JOIN
                      dbo.Sale  ON  dbo.ComFlow.SaleCode  =  dbo.Sale.SaleCode  INNER   JOIN
                      dbo.Department  ON  dbo.Department.DepartCode  =  dbo.Employee.DepartCode  INNER   JOIN
                      dbo.Department  AS  Department1  ON  Department1.DepartCode  =  dbo.Sale.DepartCode  AND  
                      dbo.Department.GrpCode  <>  Department1.GrpCode  INNER   JOIN
                      dbo.Commodity  ON  dbo.ComFlow.ComCode  =  dbo.Commodity.ComCode
WHERE      ( NOT  (dbo.ComFlow.SalType  =  N ' 流向退货 ' ))  OR
                      ( NOT  (dbo.Customer.Type  =  N ' 医药公司 ' ))
UNION   ALL
SELECT      ComFlow_1.ComFlowCode, ComFlow_1.ComCode, ComFlow_1.CustCode, ComFlow_1.DepartCode, ComFlow_1.SaleCode, ComFlow_1.EmpCode, 
                      ComFlow_1.Quantity  *  Commodity_1.ConvertRate  AS  Quantity,  CONVERT ( datetime ,  DATENAME (yyyy, ComFlow_1.FlowDate)  +   ' - '   +   DATENAME (mm, 
                      ComFlow_1.FlowDate)  +   ' - '   +   DATENAME (dd, ComFlow_1.FlowDate))  AS  FlowDate, ComFlow_1.SalType, Employee_1.DepartCode  AS  DepartIn, 
                      Sale_1.DepartCode  AS  DepartOut, ComFlow_1.Quantity  *  Commodity_1.TradePrice  *  Commodity_1.Discount  /   100   AS  Total, 
                      Department_1.GrpCode  AS  GrpCodeIn, Department1.GrpCode  AS  GrpCodeOut
FROM          dbo.ComFlow  AS  ComFlow_1  INNER   JOIN
                      dbo.Customer  AS  Customer_1  ON  ComFlow_1.SalType  IN  (N ' 自然流向 ' , N ' 自然流向退货 ' )  AND  
                      ComFlow_1.OutCustCode  =  Customer_1.CustCode  INNER   JOIN
                      dbo.CustomerRelation  AS  CustomerRelation_1  ON  ComFlow_1.ComCode  =  CustomerRelation_1.ComCode  AND  
                      CustomerRelation_1.CustCode  =  Customer_1.CustCode  INNER   JOIN
                      dbo.Employee  AS  Employee_1  ON  CustomerRelation_1.EmpCode  =  Employee_1.EmpCode  INNER   JOIN
                      dbo.Sale  AS  Sale_1  ON  ComFlow_1.SaleCode  =  Sale_1.SaleCode  INNER   JOIN
                      dbo.Department  AS  Department_1  ON  Department_1.DepartCode  =  Employee_1.DepartCode  INNER   JOIN
                      dbo.Department  AS  Department1  ON  Department1.DepartCode  =  Sale_1.DepartCode  AND  Department_1.GrpCode  <>  Department1.GrpCode  INNER   JOIN
                      dbo.Commodity  AS  Commodity_1  ON  ComFlow_1.ComCode  =  Commodity_1.ComCode
WHERE      ( NOT  (ComFlow_1.SalType  =  N ' 流向退货 ' ))  OR
                      ( NOT  (Customer_1.Type  =  N ' 医药公司 ' ))

没有想到，效果太明显了，之前需要30分钟才能运行完毕的语句只要30几秒就完成了。

这里可以看出，Or的语句可能破坏了索引的作用。使用Or进行关联虽然逻辑非常清楚，但是效率低。
使用Union虽然冗长，但是用在这里效率要高。