实战上亿数据如何秒查Word格式文档下载.docx
《实战上亿数据如何秒查Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《实战上亿数据如何秒查Word格式文档下载.docx（15页珍藏版）》请在冰豆网上搜索。
原因是：
表分区操作本身会锁表，产线还在推数据过来，这样很容易“阻塞”，“死锁”。
我想好的方案是：
建立一个新表（空表），在新表上建好表分区，然后复制数据过来。
正打算这么干。
等等！
我好像进入了一个严重的误区！
分析：
原SQL语句和业务需求，是对产线的数据做产品以及序列号的追溯，关键是查询条件里没有有规律的"
条件"
（如日期、编号），贸然做了表分区，在这里几乎没有意义！
反而会降低查询性能！
好险！
还是一步一步来，先做SQL语句分析。
一.对原SQL语句的分析1.查询语句的where条件，有大量@varin...or（@var='
'
）的片段2.where条件有like'
%'
+@var+'
3.where条件有case...end函数4.多次连接同一表查询，另外使用本身已嵌套的视图表，是不是必须，是否可替代？
5.SQL语句有*号，视图中也有*号出现二.优化设计首先是用存储过程改写，好处是设计灵活。
核心思想是：
用一个或多个查询条件（查询条件要求至少输入一个）得到临时表，每个查询条件如果查到集合，就更新这张临时表，最后汇总的时候，只需判断这个临时表是否有值。
以此类推，可以建立多个临时表，将查询条件汇总。
这样做目前来看至少两点好处：
1.省去了对变量进行=@varor（@var='
）的判断；
2.抛弃sql拼接，提高代码可读性。
再有就是在书写存储过程，这个过程中要注意：
1.尽量想办法使用临时表扫描替代全表扫描；
2.抛弃in和notin语句，使用exists和notexists替代；
3.和客户确认，模糊查询是否有必要，如没有必要，去掉like语句；
4.注意建立适当的，符合场景的索引；
5.踩死"
*"
号；
6.避免在where条件中对字段进行函数操作；
7.对实时性要求不高的报表，允许脏读（with（nolock））。
三.存储过程如果想参考优化设计片段的详细内容，请参阅SQL代码：
1/**
2*某某跟踪报表
3**/
4--execspName1'
'
公司代号'
5CREATEProcedurespName1
6@MESOrderIDnvarchar（320）,--工单号,最多30个
7@LotNamenvarchar（700）,--产品序列号,最多50个
8@DateCodenvarchar（500）,--供应商批次号,最多30个
9@BatchIDnvarchar（700）,--组装件序列号/物料批号,最多50个
10@comdefnvarchar（700）,--组装件物料编码,最多30个
11@SNCustnvarchar（1600）,--外部序列号,最多50个
12@OnPlantnvarchar（20）--平台
13AS
14BEGIN
15SETNOCOUNTON;
16/**
17*1）定义全局的临时表，先根据六个查询条件的任意一个，得出临时表结果
18**/
19CREATETABLE#FinalLotName
20（
21LotNameNVARCHAR（50）,--序列号
22SourceLotNameNVARCHAR（50）,--来源序列号
23SNCustNVARCHAR（128）--外部序列号
24）
25--1.1
26IF@LotName&
lt;
&
gt;
27BEGIN
28SELECTValINTO#WorkLotFROMfn_String_To_Table（@LotName,'
1）
29SELECTLotPK,LotNameINTO#WorkLotPKFROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotbWHEREb.Val=MMLots.LotID）
30
31--求SourceLotPK只能在这里求
32SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePKFROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPKbWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
33
34SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK2FROM#WorkSourcePKaJOIN#WorkLotPKbONa.LotPK=b.LotPK
35
36INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK2aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
37SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX1FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
38DELETEFROM#FinalLotName
39INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX1
40END
41--1.2
42IF@BatchID&
43BEGIN
44SELECTValINTO#WorkSourceLotFROMfn_String_To_Table（@BatchID,'
45IFEXISTS（SELECT1FROM#FinalLotName）--如果@LotName也不为空
46BEGIN
47SELECTa.LotName,a.SourceLotName,a.SNCustINTO#FinalLotNameX2FROM#FinalLotNameaWHEREEXISTS（SELECT1FROM#WorkSourceLotbWHEREa.SourceLotName=b.Val）
48DELETEFROM#FinalLotName
49INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX2
50END
51ELSE--@LotName条件为空
52BEGIN
53SELECTLotPKASSourceLotPK,LotNameASSourceLotNameINTO#2FROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkSourceLotbWHEREb.Val=MMLots.LotID）
54SELECTa.LotPK,a.SourceLotPKinto#21FROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#2bWHEREb.SourceLotPK=a.SourceLotPK）
55SELECTa.LotPK,a.SourceLotPK,b.SourceLotNameINTO#22FROM#21aJOIN#2bONa.SourceLotPK=b.SourceLotPK
56INSERTINTO#FinalLotNameSELECTb.LotName,a.SourceLotName,NULLFROM#22aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.LotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
57SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX21FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
58DELETEFROM#FinalLotName
59INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX21
60END
61END
62--1.3
63IF@SNCust&
64BEGIN
65SELECTValINTO#WorkCustomSNFROMfn_String_To_Table（@SNCust,'
66IFEXISTS（SELECT1FROM#FinalLotName）--前面两个条件至少有一个有值
67BEGIN
68SELECTa.LotName,a.SourceLotName,a.SNCustINTO#FinalLotNameX3FROM#FinalLotNameaWHEREEXISTS（SELECT1FROM#WorkCustomSNbWHEREa.SNCust=b.Val）
69DELETEFROM#FinalLotName
70INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX3
71END
72ELSE
73BEGIN
74SELECTa.SNMesINTO#WorkLotXFROMCO_SN_LINK_CUSTOMERaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkCustomSNbWHEREa.SNCust=b.Val）
75-------------------以下逻辑和变量1（@LotName）类似[先根据外部序列号求解序列号,再照搬第一个判断变量的方式]
76SELECTLotPK,LotNameINTO#WorkLotPKXFROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotXbWHEREb.SNMes=MMLots.LotID）
77
78--求SourceLotPK只能在这里求
79SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePKXFROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPKXbWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
80
81SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK2XFROM#WorkSourcePKXaJOIN#WorkLotPKXbONa.LotPK=b.LotPK
82
83INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK2XaJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
84SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX31FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
85DELETEFROM#FinalLotName
86INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX31
87-----------------------
88END
89END
90
91/**
92*2）定义全局的临时表，用于替换第一个全局临时表。
93**/
94CREATETABLE#FinalCO_SN
95（
96SNNVARCHAR（50）,
97SourceSNNVARCHAR（50）,
98SNCustNVARCHAR（128）,
99matl_def_idNVARCHAR（50）,--sn的物料ID
100ComMaterialsNVARCHAR（50）,--SourceSN的物料ID
101MESOrderIDNVARCHAR（20）,
102OnPlantIDNVARCHAR（20）,
103VendorIDNVARCHAR（20）,
104DateCodeNVARCHAR（20）,
105SNNoteNVARCHAR（512）
106）
107--2.1
108IF@MESOrderID&
109BEGIN
110-------------------------------将MESOrderID做特殊处理-----------------------------------
111SELECTValINTO#WorkMESOrderIDFROMfn_String_To_Table（@MESOrderID,'
112IF@OnPlant='
Comba'
113BEGIN
114UPDATE#WorkMESOrderIDSETVal='
C000'
+ValWHERELEN（Val）=9
115END
116ELSE
117BEGIN
118UPDATE#WorkMESOrderIDSETVal='
W000'
119END
120SELECTSN,MaterialID,MESOrderID,OnPlantIDINTO#WorkCO_SN1FROMCO_SN_GENERATIONaWITH（NOLOCK）
121WHERESNType='
IntSN'
ANDSNRuleName='
ProductSNRule'
ANDOnPlantID=@OnPlant
122ANDEXISTS（SELECT1FROM#WorkMESOrderIDbWHEREa.MESOrderID=b.Val）
123------------------------------------------------------------------------------------------
124--条件判断（逻辑分析）开始
125IFEXISTS（SELECT1FROM#FinalLotName）--如果前面判断的查询条件有值
126BEGIN
127--查出SourceLotName对应的查询字段
128SELECTa.SNASSourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialIDASComMaterialsINTO#SourceLotNameTableFROMCO_SN_GENERATIONaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#FinalLotNamebWHEREa.SN=b.SourceLotName）
129
130INSERTINTO#FinalCO_SN
131SELECTa.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNoteFROM#FinalLotNamea
132LEFTJOIN#WorkCO_SN1bONa.LotName=b.SN
133LEFTJOIN#SourceLotNameTablecONa.SourceLotName=c.SourceLotName
134LEFTJOINCO_SN_LINK_CUSTOMERdWITH（NOLOCK）ONa.LotName=d.SNMes
135END
136ELSE
137BEGIN
138--已知SN集合求解对应的SourceSN和SNCust集合------------------------------------------
139SELECTLotPK,LotNameINTO#WorkLotPK410FROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkCO_SN1bWHEREb.SN=MMLots.LotID）
140SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePK420FROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPK410bWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
141SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK430FROM#WorkSourcePK420aJOIN#WorkLotPK410bONa.LotPK=b.LotPK
142INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK430aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
143
144SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX440FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
145DELETEFROM#FinalLotName
146INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX440
147-------------------------------------------------------------------------------------
148SELECTa.SNASSourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialIDASComMaterialsINTO#SourceLotNameTable2FROMCO_SN_GENERATIONaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#FinalLotNamebWHEREa.SN=b.SourceLotName）
149
150INSERTINTO#FinalCO_SN
151SELECTa.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNoteFROM#FinalLotNamea
152LEFTJOIN#WorkCO_SN1bONa.LotName=b.SN
153LEFTJOIN#SourceLotNameTable2cONa.SourceLotName=c.SourceLotName
154EN
升级会员 