实战上亿数据如何秒查Word格式文档下载.docx

实战上亿数据如何秒查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