IF EXISTS(SELECT 1 FROM#FinalLotName)
　　BEGIN
　　IF EXISTS(SELECT 1 FROM#FinalCO_SN)
　　BEGIN--3.1
　　SELECT a.matl_def_id，b.Descript，a.MESOrderID AS pom_order_id，a.SN AS LotName，a.SourceSN AS ComLot，
　　a.ComMaterials，c.Descript AS ComMatDes，a.VendorID，a.DateCode，a.SNNote，
　　OnPlantID，SNCust FROM#FinalCO_SN a
　　JOIN MMDefinitions b WITH(NOLOCK)ON a.matl_def_id=b.DefID
　　JOIN MMDefinitions c WITH(NOLOCK)ON a.ComMaterials=c.DefID
　　WHERE NOT EXISTS(select distinct SN，SourceSN from#FinalCO_SN x
　　where x.SN=a.SourceSN and x.SourceSN=a.SN)
　　END
　　ELSE
　　BEGIN--3.2
　　--3.2.1求解SN的必查字段
　　SELECT SN，MaterialID，MESOrderID，OnPlantID INTO#FinalSNX1 FROM CO_SN_GENERATION a WITH(NOLOCK)
　　WHERE SNType='IntSN'AND SNRuleName='ProductSNRule'AND OnPlantID=@OnPlant
　　AND EXISTS(SELECT 1 FROM#FinalLotName b WHERE a.SN=b.LotName)
　　--3.2.2求解SourceSN的必查字段
　　SELECT a.SN AS SourceLotName，a.VendorID，a.DateCode，a.SNNote，a.MaterialID AS ComMaterials INTO#FinalSNX2 FROM CO_SN_GENERATION a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#FinalLotName b WHERE a.SN=b.SourceLotName)
　　SELECT b.MaterialID AS matl_def_id，x.Descript，b.MESOrderID AS pom_order_id，b.SN AS LotName，c.SourceLotName AS ComLot，c.ComMaterials，y.Descript AS ComMatDes，c.VendorID，c.DateCode，c.SNNote，b.OnPlantID，a.SNCust
　　FROM#FinalLotName a
　　LEFT JOIN#FinalSNX1 b ON a.LotName=b.SN
　　LEFT JOIN#FinalSNX2 c ON a.SourceLotName=c.SourceLotName
　　JOIN MMDefinitions x WITH(NOLOCK)ON b.MaterialID=x.DefID
　　JOIN MMDefinitions y WITH(NOLOCK)ON c.ComMaterials=y.DefID
　　WHERE NOT EXISTS(
　　SELECT DISTINCT*FROM#FinalLotName z
　　WHERE z.LotName=a.SourceLotName and z.SourceLotName=a.LotName
　　)
　　END
　　END
　　ELSE
　　BEGIN
　　IF EXISTS(SELECT 1 FROM#FinalCO_SN)
　　BEGIN--3.3
　　SELECT a.matl_def_id，b.Descript，a.MESOrderID AS pom_order_id，a.SN AS LotName，a.SourceSN AS ComLot，
　　a.ComMaterials，c.Descript AS ComMatDes，a.VendorID，a.DateCode，a.SNNote，
　　OnPlantID，SNCust FROM#FinalCO_SN a
　　JOIN MMDefinitions b WITH(NOLOCK)ON a.matl_def_id=b.DefID
　　JOIN MMDefinitions c WITH(NOLOCK)ON a.ComMaterials=c.DefID
　　WHERE NOT EXISTS(select distinct SN，SourceSN from#FinalCO_SN x
　　where x.SN=a.SourceSN and x.SourceSN=a.SN)
　　END
　　ELSE
　　BEGIN--3.4
　　PRINT'There is no queryable condition，please enter at less a query conditon.'
　　END
　　END
　　END
　　GO
　　虽然牺牲了代码的可读性，但创造了性能价值。本人水平有限，还请各位不吝赐教！
　　后，将SSRS报表替换成此存储过程后，SQL查询分析器是秒查的。B/S前端用时1~2秒！
　　四.总结
　　平常的你是否偶尔会因急于完成任务而书写一堆性能极低的SQL语句呢？写出可靠性能的SQL语句不难，难的是习惯。
　　本文的优化思想很简单，关键点是避免全表扫描&注重SQL语句写法&索引，另外，如果你查询的表有可能会在查询时段更新，而实际业务需求允许脏读，可加with(nolock)预防查询被更新事物阻塞。