⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 blog.csdn.net/chenleixing/article/details/44994571 「Java我人生」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注**微信公众号：【芋道源码】**有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度由从半小时以上（甚至查不出）到秒查的质变。从修改 SQL 查询语句逻辑到决定创建存储过程实现，花了我 3 天多的时间，在此总结一下，希望对朋友们有帮助。

数据背景

首先项目是西门子中国在我司实施部署的 MES 项目，由于项目是在产线上运作(3 years+)，数据累积很大。

在项目的数据库中，大概上亿条数据的表有 5 个以上，千万级数据的表 10 个以上，百万级数据的表，很多…

（历史问题，当初实施无人监管，无人监控数据库这块的性能问题。PS：我刚入职不久…）

不多说，直接贴西门子中国的开发人员在我司开发的 SSRS 报表中的 SQL 语句：

select distinct b.MaterialID as matl_def_id, c.Descript, case when right(b.MESOrderID, 12) < '001000000000' then right(b.MESOrderID, 9)
else right(b.MESOrderID, 12) end  as pom_order_id, a.LotName, a.SourceLotName as ComLot,
e.DefID as ComMaterials, e.Descript as ComMatDes, d.VendorID, d.DateCode,d.SNNote, b.OnPlantID,a.SNCUST
from
(
    select m.lotname, m.sourcelotname, m.opetypeid, m.OperationDate,n.SNCUST from View1 m
    left join co_sn_link_customer as n on n.SNMes=m.LotName
    where
    ( m.LotName in (select val from fn_String_To_Table(@sn,',',1)) or (@sn) = '') and
    ( m.sourcelotname in (select val from fn_String_To_Table(@BatchID,',',1)) or (@BatchID) = '')
    and (n.SNCust like '%'+ @SN_ext + '%' or (@SN_ext)='')
) a
left join
(
    select * from Table1 where SNType = 'IntSN'
    and SNRuleName = 'ProductSNRule'
    and OnPlantID=@OnPlant
) b on b.SN = a.LotName
inner join MMdefinitions as c on c.DefID = b.MaterialID
left join  Table1 as d on d.SN = a.SourceLotName
inner join MMDefinitions as e on e.DefID = d.MaterialID
where not exists (
 select distinct LotName, SourceLotName from ELCV_ASSEMBLE_OPS
where LotName = a.SourceLotName and SourceLotName = a.LotName
)
and (d.DateCode in (select val from fn_String_To_Table(@DCode,',',1)) or (@DCode) = '')
and (d.SNNote  like '%'+@SNNote+'%' or (@SNNote) = '')
and ((case when right(b.MESOrderID, 12) < '001000000000' then right(b.MESOrderID, 9)
else right(b.MESOrderID, 12) end) in (select val from fn_String_To_Table(@order_id,',',1)) or (@order_id) = '')
and (e.DefID in (select val from fn_String_To_Table(@comdef,',',1)) or (@comdef) = '')
--View1是一个嵌套两层的视图(出于保密性，实际名称可能不同)，里面有一张上亿数据的表和几张千万级数据的表做左连接查询
--Table1是一个数据记录超过1500万的表

这个查询语句，实际上通过我的检测和调查，在 B/S 系统前端已无法查出结果，半小时，一小时...

因为我直接在 SQL 查询分析器查，半小时都没有结果。（原因是里面对一张上亿级数据表和 3 张千万级数据表做全表扫描查询）

不由感慨，西门子中国的素质（或者说责任感）就这样？下面说说我的分析和走的弯路（思维误区），希望对你也有警醒。

探索和误区

首先相关表的索引，没有建全的，把索引给建上。

索引这步完成后，发现情况还是一样，查询速度几乎没有改善。后来想起相关千万级数据以上的表，都还没有建立表分区。于是考虑建立表分区以及数据复制的方案。

**这里有必要说明下：**我司报表用的是一个专门的数据库服务器，数据从产线订阅而来。就是常说的“读写分离”。

如果直接在原表上建立表分区，你会发现执行表分区的事物会直接死锁。原因是：表分区操作本身会锁表，产线还在推数据过来，这样很容易“阻塞”，“死锁”。

我想好的方案是：建立一个新表（空表），在新表上建好表分区，然后复制数据过来。

正打算这么干。等等！我好像进入了一个严重的误区！

**分析：**原 SQL 语句和业务需求，是对产线的数据做产品以及序列号的追溯，关键是查询条件里没有有规律的”条件”（如日期、编号），贸然做了表分区，在这里几乎没有意义！反而会降低查询性能！

好险！还是一步一步来，先做 SQL 语句分析。

对原 SQL 语句的分析

对原 SQL 语句的分析如下：

查询语句的 where 条件，有大量 @var in … or (@var =”) 的片段。
where 条件有 like ‘%’+@var+’%’。
where 条件有 case … end 函数。
多次连接同一表查询，另外使用本身已嵌套的视图表，是不是必须，是否可替代？
SQL 语句有号，视图中也有号出现。

优化设计

首先是用存储过程改写，好处是设计灵活。

**核心思想是：**用一个或多个查询条件（查询条件要求至少输入一个）得到临时表，每个查询条件如果查到集合，就更新这张临时表，最后汇总的时候，只需判断这个临时表是否有值。

以此类推，可以建立多个临时表，将查询条件汇总。

这样做目前来看至少两点好处：

省去了对变量进行 =@var or (@var=”)的判断。
抛弃 SQL 拼接，提高代码可读性。

再有就是在书写存储过程，这个过程中要注意：

尽量想办法使用临时表扫描替代全表扫描。
抛弃 in 和 not in 语句，使用 exists 和 not exists 替代。
和客户确认，模糊查询是否有必要，如没有必要，去掉 like 语句。
注意建立适当的，符合场景的索引。
踩死 “*” 号。
避免在 where 条件中对字段进行函数操作。
对实时性要求不高的报表，允许脏读（with(nolock)）。

存储过程

如果想参考优化设计片段的详细内容，请参阅 SQL 代码：

/**
 * 某某跟踪报表
 **/
--exec spName1 '','','','','','','公司代号'
CREATE Procedure spName1
   @MESOrderID nvarchar(320), --工单号,最多30个
   @LotName nvarchar(700),    --产品序列号,最多50个
   @DateCode nvarchar(500),   --供应商批次号,最多30个
   @BatchID nvarchar(700),    --组装件序列号/物料批号,最多50个
   @comdef nvarchar(700),     --组装件物料编码,最多30个
   @SNCust nvarchar(1600),    --外部序列号,最多50个
   @OnPlant nvarchar(20)      --平台
AS
BEGIN
    SET NOCOUNT ON;
    /**
     * 1)定义全局的临时表，先根据六个查询条件的任意一个，得出临时表结果
     **/
    CREATE TABLE #FinalLotName
    (
        LotName NVARCHAR(50),       --序列号
        SourceLotName NVARCHAR(50), --来源序列号
        SNCust NVARCHAR(128)        --外部序列号
    )
    --1.1
    IF @LotName<>''
    BEGIN
        SELECT Val INTO #WorkLot FROM fn_String_To_Table(@LotName,',',1)
        SELECT LotPK,LotName INTO #WorkLotPK FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLot b WHERE b.Val=MMLots.LotID)

        --求SourceLotPK只能在这里求
        SELECT a.LotPK,a.SourceLotPK into #WorkSourcePK FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotPK b WHERE b.LotPK=a.LotPK) AND a.SourceLotPK IS NOT NULL

        SELECT a.LotPK,a.SourceLotPK,b.LotName INTO #WorkSourcePK2 FROM #WorkSourcePK a JOIN #WorkLotPK b ON a.LotPK=b.LotPK

        INSERT INTO #FinalLotName SELECT a.LotName,b.LotName AS SourceLotName,NULL FROM #WorkSourcePK2 a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定
        SELECT a.LotName,a.SourceLotName,b.SNCust INTO #FinalLotNameX1 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
        DELETE FROM #FinalLotName
        INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX1
    END
    --1.2
    IF @BatchID<>''
    BEGIN
        SELECT Val INTO #WorkSourceLot FROM fn_String_To_Table(@BatchID,',',1)
        IF EXISTS(SELECT 1 FROM #FinalLotName)--如果@LotName也不为空
        BEGIN
            SELECT a.LotName,a.SourceLotName,a.SNCust INTO #FinalLotNameX2 FROM #FinalLotName a WHERE EXISTS(SELECT 1 FROM #WorkSourceLot b WHERE a.SourceLotName=b.Val)
            DELETE FROM #FinalLotName
            INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX2
        END
        ELSE --@LotName条件为空
        BEGIN
            SELECT LotPK AS SourceLotPK,LotName AS SourceLotName INTO #2 FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkSourceLot b WHERE b.Val=MMLots.LotID)
            SELECT a.LotPK,a.SourceLotPK into #21 FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #2 b WHERE b.SourceLotPK=a.SourceLotPK)
            SELECT a.LotPK,a.SourceLotPK,b.SourceLotName INTO #22 FROM #21 a JOIN #2 b ON a.SourceLotPK=b.SourceLotPK
            INSERT INTO #FinalLotName SELECT b.LotName,a.SourceLotName,NULL FROM #22 a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定
            SELECT a.LotName,a.SourceLotName,b.SNCust INTO #FinalLotNameX21 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
            DELETE FROM #FinalLotName
            INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX21
        END
    END
    --1.3
    IF @SNCust<>''
    BEGIN
        SELECT Val INTO #WorkCustomSN FROM fn_String_To_Table(@SNCust,',',1)
        IF EXISTS(SELECT 1 FROM #FinalLotName)--前面两个条件至少有一个有值
        BEGIN
            SELECT a.LotName,a.SourceLotName,a.SNCust INTO #FinalLotNameX3 FROM #FinalLotName a WHERE EXISTS(SELECT 1 FROM #WorkCustomSN b WHERE a.SNCust=b.Val)
            DELETE FROM #FinalLotName
            INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX3
        END
        ELSE
        BEGIN
            SELECT a.SNMes INTO #WorkLotX FROM CO_SN_LINK_CUSTOMER a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkCustomSN b WHERE a.SNCust=b.Val)
            -------------------以下逻辑和变量1(@LotName)类似[先根据外部序列号求解序列号,再照搬第一个判断变量的方式]
            SELECT LotPK,LotName INTO #WorkLotPKX FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotX b WHERE b.SNMes=MMLots.LotID)

            --求SourceLotPK只能在这里求
            SELECT a.LotPK,a.SourceLotPK into #WorkSourcePKX FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotPKX b WHERE b.LotPK=a.LotPK) AND a.SourceLotPK IS NOT NULL

            SELECT a.LotPK,a.SourceLotPK,b.LotName INTO #WorkSourcePK2X FROM #WorkSourcePKX a JOIN #WorkLotPKX b ON a.LotPK=b.LotPK

            INSERT INTO #FinalLotName SELECT a.LotName,b.LotName AS SourceLotName,NULL FROM #WorkSourcePK2X a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定
            SELECT a.LotName,a.SourceLotName,b.SNCust INTO #FinalLotNameX31 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
            DELETE FROM #FinalLotName
            INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX31
            -----------------------
        END
    END

    /**
     * 2)定义全局的临时表，用于替换第一个全局临时表。
     **/
    CREATE TABLE #FinalCO_SN
    (
        SN NVARCHAR(50),
        SourceSN NVARCHAR(50),
        SNCust NVARCHAR(128),
        matl_def_id NVARCHAR(50),--sn的物料ID
        ComMaterials NVARCHAR(50),  --SourceSN的物料ID
        MESOrderID NVARCHAR(20),
        OnPlantID NVARCHAR(20),
        VendorID NVARCHAR(20),
        DateCode NVARCHAR(20) ,
        SNNote NVARCHAR(512)
    )
    --2.1
    IF @MESOrderID<>''
    BEGIN
        -------------------------------将MESOrderID做特殊处理-----------------------------------
        SELECT Val INTO #WorkMESOrderID FROM fn_String_To_Table(@MESOrderID,',',1)
        IF @OnPlant='Comba'
        BEGIN
            UPDATE #WorkMESOrderID SET Val='C000'+Val WHERE LEN(Val)=9
        END
        ELSE
        BEGIN
            UPDATE #WorkMESOrderID SET Val='W000'+Val WHERE LEN(Val)=9
        END
        SELECT SN,MaterialID,MESOrderID,OnPlantID INTO #WorkCO_SN1 FROM CO_SN_GENERATION a WITH(NOLOCK)
        WHERE SNType='IntSN' AND SNRuleName = 'ProductSNRule' AND OnPlantID=@OnPlant
        AND EXISTS(SELECT 1 FROM #WorkMESOrderID b WHERE a.MESOrderID=b.Val)
        ------------------------------------------------------------------------------------------
        --条件判断(逻辑分析)开始
        IF EXISTS(SELECT 1 FROM #FinalLotName)--如果前面判断的查询条件有值
        BEGIN
            --查出SourceLotName对应的查询字段
            SELECT a.SN AS SourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialID AS ComMaterials INTO #SourceLotNameTable FROM CO_SN_GENERATION a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #FinalLotName b WHERE a.SN=b.SourceLotName)

            INSERT INTO #FinalCO_SN
            SELECT a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
            LEFT JOIN #WorkCO_SN1 b ON a.LotName=b.SN
            LEFT JOIN #SourceLotNameTable c ON a.SourceLotName=c.SourceLotName
            LEFT JOIN CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON a.LotName=d.SNMes
        END
        ELSE
        BEGIN
            --已知SN集合求解对应的SourceSN和SNCust集合------------------------------------------
            SELECT LotPK,LotName INTO #WorkLotPK410 FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkCO_SN1 b WHERE b.SN=MMLots.LotID)
            SELECT a.LotPK,a.SourceLotPK into #WorkSourcePK420 FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotPK410 b WHERE b.LotPK=a.LotPK) AND a.SourceLotPK IS NOT NULL
            SELECT a.LotPK,a.SourceLotPK,b.LotName INTO #WorkSourcePK430 FROM #WorkSourcePK420 a JOIN #WorkLotPK410 b ON a.LotPK=b.LotPK
            INSERT INTO #FinalLotName SELECT a.LotName,b.LotName AS SourceLotName,NULL FROM #WorkSourcePK430 a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定

            SELECT a.LotName,a.SourceLotName,b.SNCust INTO #FinalLotNameX440 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
            DELETE FROM #FinalLotName
            INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #FinalLotNameX440
            -------------------------------------------------------------------------------------
            SELECT a.SN AS SourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialID AS ComMaterials INTO #SourceLotNameTable2 FROM CO_SN_GENERATION a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #FinalLotName b WHERE a.SN=b.SourceLotName)

            INSERT INTO #FinalCO_SN
            SELECT a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
            LEFT JOIN #WorkCO_SN1 b ON a.LotName=b.SN
            LEFT JOIN #SourceLotNameTable2 c ON a.SourceLotName=c.SourceLotName
        END
    END
    --2.2
    IF @DateCode<>''
    BEGIN
        SELECT Val INTO #WorkDateCode FROM fn_String_To_Table(@DateCode,',',1)
        --此@DataCode条件求解出来的是SourceSN
        SELECT SN AS SourceSN,MaterialID AS ComMaterials,VendorID,DateCode,SNNote INTO #WorkSourceSNT1 FROM CO_SN_GENERATION a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkDateCode b WHERE a.DateCode=b.Val)
        ----------------------------------------------------------------------------------------------------
        --条件判断(逻辑分析)开始
        IF EXISTS(SELECT 1 FROM #FinalCO_SN)--如果前面判断的查询条件有值
        BEGIN
            SELECT a.LotName,a.SourceLotName,a.SNCust,a.MaterialID,a.ComMaterials,a.MESOrderID,a.OnPlantID,a.VendorID,a.DateCode,a.SNNote INTO #TMP51 FROM #FinalCO_SN a WHERE EXISTS (SELECT 1 FROM #WorkDateCode b WHERE a.DateCode=b.Val)
            DELETE FROM #FinalCO_SN
            INSERT INTO #FinalCO_SN SELECT LotName,SourceLotName,SNCust,MaterialID,ComMaterials,MESOrderID,OnPlantID,VendorID,DateCode,SNNote FROM #TMP51
        END
        ELSE
        BEGIN
            IF EXISTS(SELECT 1 FROM #FinalLotName)
            BEGIN
            --查出SourceLotName对应的查询字段
            SELECT a.SourceSN,a.VendorID,a.DateCode,a.SNNote,a.ComMaterials INTO #SourceLTX5 FROM #WorkSourceSNT1 a WHERE EXISTS(SELECT 1 FROM #FinalLotName b WHERE a.SourceSN=b.SourceLotName)
            --查出SN对应的查询字段
            SELECT SN,MaterialID,MESOrderID,OnPlantID INTO #WorkSNT510 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)

            INSERT INTO #FinalCO_SN
            SELECT a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
            LEFT JOIN #WorkSNT510 b ON a.LotName=b.SN
            LEFT JOIN #WorkSourceSNT1 c ON a.SourceLotName=c.SourceSN
            LEFT JOIN CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON a.LotName=d.SNMes

            END
            ELSE
            BEGIN
                --已知SourceSN集合求解对应的SN和SNCust集合------------------------------------------
                SELECT LotPK AS SourceLotPK,LotName AS SrouceLotName INTO #WorkLotX510 FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkSourceSNT1 b WHERE b.SourceSN=MMLots.LotID)
                SELECT a.LotPK,a.SourceLotPK into #WorkLotX520 FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotX510 b WHERE b.SourceLotPK=a.SourceLotPK)
                SELECT a.LotPK,a.SourceLotPK,b.SrouceLotName INTO #WorkLotX530 FROM #WorkLotX520 a JOIN #WorkLotX510 b ON a.SourceLotPK=b.SourceLotPK

                INSERT INTO #FinalLotName SELECT b.LotName,a.SrouceLotName,NULL FROM #WorkLotX530 a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定

                SELECT a.LotName,a.SourceLotName,b.SNCust INTO #WorkLotX540 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
                DELETE FROM #FinalLotName
                INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #WorkLotX540
                -------------------------------------------------------------------------------------
                SELECT SN,MaterialID,MESOrderID,OnPlantID INTO #WorkLotX550 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)

                INSERT INTO #FinalCO_SN
                SELECT a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
                LEFT JOIN #WorkLotX550 b ON a.LotName=b.SN
                LEFT JOIN #WorkSourceSNT1 c ON a.SourceLotName=c.SourceSN
            END
        END
    END
    --2.3
    IF @comdef<>''
    BEGIN
        SELECT Val INTO #WorkComdef FROM fn_String_To_Table(@comdef,',',1)
        --此@comdef条件求解出来的是SourceSN
        SELECT SN AS SourceSN,MaterialID AS ComMaterials,VendorID,DateCode,SNNote INTO #WorkSourceSNT16 FROM CO_SN_GENERATION a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkComdef b WHERE a.MaterialID=b.Val)
        ----------------------------------------------------------------------------------------------------
        --条件判断(逻辑分析)开始
        IF EXISTS(SELECT 1 FROM #FinalCO_SN)--如果前面判断的查询条件有值
        BEGIN
            SELECT a.LotName,a.SourceLotName,a.SNCust,a.MaterialID,a.ComMaterials,a.MESOrderID,a.OnPlantID,a.VendorID,a.DateCode,a.SNNote INTO #TMP516 FROM #FinalCO_SN a WHERE EXISTS (SELECT 1 FROM #WorkComdef b WHERE a.matl_def_id=b.Val)
            DELETE FROM #FinalCO_SN
            INSERT INTO #FinalCO_SN SELECT LotName,SourceLotName,SNCust,MaterialID,ComMaterials,MESOrderID,OnPlantID,VendorID,DateCode,SNNote FROM #TMP516
        END
        ELSE
        BEGIN
            IF EXISTS(SELECT 1 FROM #FinalLotName)
            BEGIN
            --查出SourceLotName对应的查询字段
            SELECT a.SourceSN,a.VendorID,a.DateCode,a.SNNote,a.ComMaterials INTO #SourceLTX56 FROM #WorkSourceSNT16 a WHERE EXISTS(SELECT 1 FROM #FinalLotName b WHERE a.SourceSN=b.SourceLotName)
            --查出SN对应的查询字段
            SELECT SN,MaterialID,MESOrderID,OnPlantID INTO #WorkSNT5106 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)

            INSERT INTO #FinalCO_SN
            SELECT a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
            LEFT JOIN #WorkSNT5106 b ON a.LotName=b.SN
            LEFT JOIN #WorkSourceSNT16 c ON a.SourceLotName=c.SourceSN
            LEFT JOIN CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON a.LotName=d.SNMes

            END
            ELSE
            BEGIN
                --已知SourceSN集合求解对应的SN和SNCust集合------------------------------------------
                SELECT LotPK AS SourceLotPK,LotName AS SrouceLotName INTO #WorkLotX5106 FROM MMLots WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkSourceSNT16 b WHERE b.SourceSN=MMLots.LotID)
                SELECT a.LotPK,a.SourceLotPK into #WorkLotX5206 FROM MMLotOperations a WITH(NOLOCK) WHERE EXISTS(SELECT 1 FROM #WorkLotX5106 b WHERE b.SourceLotPK=a.SourceLotPK)
                SELECT a.LotPK,a.SourceLotPK,b.SrouceLotName INTO #WorkLotX5306 FROM #WorkLotX5206 a JOIN #WorkLotX5106 b ON a.SourceLotPK=b.SourceLotPK

                INSERT INTO #FinalLotName SELECT b.LotName,a.SrouceLotName,NULL FROM #WorkLotX5306 a JOIN (SELECT LotPK,LotName FROM MMLots WITH(NOLOCK) ) b on a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待确定

                SELECT a.LotName,a.SourceLotName,b.SNCust INTO #WorkLotX5406 FROM #FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON a.LotName=b.SNMes
                DELETE FROM #FinalLotName
                INSERT INTO #FinalLotName SELECT LotName,SourceLotName,SNCust FROM #WorkLotX5406
                -------------------------------------------------------------------------------------
                SELECT SN,MaterialID,MESOrderID,OnPlantID INTO #WorkLotX5506 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)

                INSERT INTO #FinalCO_SN
                SELECT a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM #FinalLotName a
                LEFT JOIN #WorkLotX5506 b ON a.LotName=b.SN
                LEFT JOIN #WorkSourceSNT16 c ON a.SourceLotName=c.SourceSN
            END
        END
    END

    /**
     * 3)条件判断结束
     **/
    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）预防查询被更新事物阻塞。

芋道源码 —— 纯源码解析博客

耗时 3 天，上亿数据如何做到秒级查询？

数据背景

探索和误区

对原 SQL 语句的分析

优化设计

存储过程

总结