Recently I have been busy optimizing a statement of the group company. After optimization, the report query speed changes from more than half an hour (or even no check) to second check. From modifying SQL query statement logic to deciding to create stored procedure implementation, it took me more than 3 days. Here is a summary, I hope it will be helpful to friends.
Data background
The first project is the MES project implemented and deployed by Siemens China in our company. Since the project is operated on the production line (3 years+), the data accumulation is very large.
In the project database, there are more than 5 tables of hundreds of millions of data, more than 10 tables of tens of millions of data, tables of millions of data, a lot of…
(Historical problems, the implementation of unmanned supervision, unmanned monitoring of the performance of the database. PS: I just joined the company…)
Without further ado, please directly paste the SQL statement in SSRS report developed by Siemens China developer:
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 is a nested two-layer view (the actual name may be different for confidentiality reasons), Table1 is a table with more than 15 million recordsCopy the codeThis query statement, in fact, through my detection and investigation, in the FRONT end of the B/S system has been unable to find the results, half an hour, an hour…
Because I directly in the SQL query analyzer, half an hour no results. SQL > alter table scan query; SQL > alter table scan query;
Not feeling, Siemens China’s quality (or sense of responsibility) so? Here is my analysis and take a detour (thinking error), I hope you also have a warning.
Exploration and misunderstanding
First of all related table index, did not build all, to build the index.
After the indexing step is complete, the situation remains the same, with little improvement in query speed. Later reminded of the related tens of millions of data above the table, have not established table partition. Then consider the establishment of table partitioning and data replication scheme.
It is necessary to clarify here: our company uses a special database server for the report, and the data is subscribed from the production line. This is often referred to as “read-write separation”.
If you create a table partition directly on the original table, you will find that the object performing the table partition will deadlock directly. The reason for this is that the table partitioning operation itself locks the table, and the production line is still pushing data, which can easily “block” and “deadlock”.
I think a good solution is: create a new table (empty table), create a table partition on the new table, and then copy the data over.
I’m going to do that. Wait a minute! I seem to have entered a serious mistake!
Analysis: the original SQL statement and business requirements, is the data of the production line to do product and serial number traceability, the key is that there is no regular query conditions “conditions” (such as date, number), rushed to do the table partition, here almost no meaning! Instead, it will degrade query performance!
That was close! Or step by step, do SQL statement analysis first.
Analysis of the original SQL statement
The analysis of the original SQL statement is as follows:
- @var in… Or (@var = “) snippet.
- Where like ‘%’ +@var+ ‘%’
- Where there is case… End function.
- Is it necessary and alternative to join the same table query multiple times and use its own nested visual chart?
- SQL statements have numbers, and numbers appear in views.
The optimization design
The first is to use stored procedures for rewriting, which has the benefit of flexible design.
The core idea is: use one or more query conditions (query conditions require at least one input) to get a temporary table, each query condition if the collection is found, update the temporary table, the final summary, only need to judge whether the temporary table has a value.
Similarly, you can create multiple temporary tables to aggregate query criteria.
There are at least two advantages to this so far:
- The variable =@var or (@var= “) judgment is omitted.
- Discard SQL concatenation to improve code readability.
In writing stored procedures, note:
- Try to use a temporary table scan instead of a full table scan.
- Discard in and not in statements and use exists and not exists instead.
- Confirm with the customer whether fuzzy query is necessary, if not, remove the like statement.
- Be careful to set up appropriate, scenario-appropriate indexes.
- Trampled to death “*”.
- Avoid functional manipulation of fields in WHERE conditions.
- Dirty read (with(NOLock)) is allowed for reports that do not require high real-time performance.
The stored procedure
For details of the optimized design snippet, see SQL code:
/**
* 某某跟踪报表
**/
--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
Copy the codeThe readability of the code is sacrificed, but the performance value is created. My level is limited, but also please do not hesitate to give advice!
Finally, after replacing the SSRS report with this stored procedure, the SQL query analyzer is second to look up. B/S front end takes 1~2 seconds!
conclusion
Do you occasionally write a bunch of poorly performing SQL statements in a rush to get things done? Writing SQL statements with reliable performance is easy; getting used to it is hard.
The optimization idea of this paper is very simple, the key point is to avoid full table scan & focus on SQL statement writing & index.
In addition, if the table you are querying is likely to update during the query period, and the actual business requirement allows dirty reads, you can add with (NOLock) to prevent the query from being blocked by the update items.
Source: sohu. Gg/jIp59N
Welcome to follow my wechat public account “Code farming breakthrough”, share Python, Java, big data, machine learning, artificial intelligence and other technologies, pay attention to code farming technology improvement, career breakthrough, thinking transition, 200,000 + code farming growth charge first stop, accompany you have a dream to grow together