帮忙优化一条MSSQL查询语句,该如何处理

2012-04-18

帮忙优化一条MSSQL查询语句请帮忙优化一条MSSQL查询语句，查询返回的记录行有94236行，用时5秒！语句如下：--

帮忙优化一条MSSQL查询语句
请帮忙优化一条MSSQL查询语句，查询返回的记录行有94236行，用时5秒！语句如下：

--序列号跟踪报表
Select ts.FSerialNum As FSerialNum,tk.FName As FStockName, t.FNumber as FItemnumber,t.FName As FItemName,ta.FName As FAuxPropName,t.FModel,
'预入库' As FStatus,tk.FNumber As FStockNumber, '仓库调拨' As FLasttranType --,1 As FSumSort,1 AS FOrder
From ICSerial ts
Inner Join ICStockBill tb On tb.FInterID=ts.FInterID
Inner Join ICSerial_F1 ts1 On ts1.FSerialClassID=ts.FSerialClassID And ts1.FSerialID=ts.FSerialID
Inner Join t_ICItem t On t.FItemID=ts.FItemID
Left Join t_AuxItem ta On ta.FItemID=ts.FAuxPropID
Left Join t_Stock tk On tk.FItemID=ts.FStockID
Left Join t_StockPlace tp On tp.FSPID=ts.FStockPlace
Left Join t_StockPlace tpo On tpo.FSPID=ts.FOutStockPlace
Where ts.FSerialClassID=1AND ts.FTrantypeID = 41 AND ts.FValid=0
UNION ALL
Select ts.FSerialNum As FSerialNum,tk.FName As FStockName, t.FNumber as FItemnumber,
t.FName As FItemName,ta.FName As FAuxPropName,t.FModel,
Case When ts.FTranTypeID In (1,2,5,10,40) And tb.FRob=1 And ts.FValid=0 Then '预入库'
When ts.FTranTypeID In (1,2,5,10,40) And tb.FRob=1 And ts.FValid=1 Then '在库'
When ts.FTranTypeID In (1,2,5,10,40) And tb.FRob=-1 And ts.FValid=0 Then '预退库'
When ts.FTranTypeID In (1,2,5,10,40) And tb.FRob=-1 And ts.FValid=1 Then '退库'
When ts.FTranTypeID In (21,24,28,29,43) And tb.FRob=-1 And ts.FValid=0 Then '预入库'
When ts.FTranTypeID In (21,24,28,29,43) And tb.FRob=-1 And ts.FValid=1 Then '在库'
When ts.FTranTypeID In (21,24,28,29,43) And tb.FRob=1 And ts.FValid=0 Then '预出库'
When ts.FTranTypeID In (21,24,28,29,43) And tb.FRob=1 And ts.FValid=1 Then '出库'
When ts.FTranTypeID =41 And ts.FValid=1 Then '在库'
When ts.FTranTypeID =41 And ts.FValid=0 Then '预出库' End As FStatus,
tk.FNumber As FStockNumber,
Case When ts.FTranTypeID=1 Then '外购入库'
When ts.FTranTypeID=2 Then '产品入库'
When ts.FTranTypeID=5 Then '委外加工入库'
When ts.FTranTypeID=10 Then '其他入库'
When ts.FTranTypeID=21 Then '销售出库'
When ts.FTranTypeID=24 Then '生产领料'
When ts.FTranTypeID=28 Then '委外加工发出'
When ts.FTranTypeID=29 Then '其他出库'
When ts.FTranTypeID=40 Then '盘盈入库'
When ts.FTranTypeID=41 Then '仓库调拨'
When ts.FTranTypeID=43 Then '盘亏毁损'
When ts.FTranTypeID=-1 Then '初始化' End As FLasttranType --,1 As FSumSort,0 AS FOrder
From ICSerial ts
Inner Join ICStockBill tb On tb.FInterID=ts.FInterID
Inner Join ICSerial_F1 ts1 On ts1.FSerialClassID=ts.FSerialClassID And ts1.FSerialID=ts.FSerialID
Inner Join t_ICItem t On t.FItemID=ts.FItemID
Left Join t_AuxItem ta On ta.FItemID=ts.FAuxPropID
Left Join t_Stock tk On tk.FItemID=(case when ts.FTranTypeID=41 and ts.FValid=0 Then ts.foutstockid else ts.FStockID end)
Left Join t_StockPlace tp On tp.FSPID=(case when ts.FTranTypeID=41 and ts.FValid=0 Then ts.FoutStockPlace else ts.FStockPlace end )
Left Join t_StockPlace tpo On tpo.FSPID=ts.FOutStockPlace
Where ts.FSerialClassID=1
Union All
Select ts.FSerialNum As FSerialNum,tk.FName As FStockName, t.FNumber as FItemnumber,
t.FName As FItemName,ta.FName As FAuxPropName,t.FModel,Case ts.FValid When 1 Then '在库' Else '预入库' End As FStatus,tk.FNumber As FStockNumber,
'初始化' As FLasttranType --,1 As FSumSort,0 AS FOrder
From ICSerial ts
Inner Join IcInvInitial tb On tb.FSNListID=ts.FSNListID
Inner Join ICSerial_F1 ts1 On ts1.FSerialClassID=ts.FSerialClassID And ts1.FSerialID=ts.FSerialID
Inner Join t_ICItem t On t.FItemID=ts.FItemID

Left Join t_AuxItem ta On ta.FItemID=ts.FAuxPropID
Left Join t_Stock tk On tk.FItemID=(case when ts.FTranTypeID=41 and ts.FValid=0 Then ts.foutstockid else ts.FStockID end)
Left Join t_StockPlace tp On tp.FSPID=(case when ts.FTranTypeID=41 and ts.FValid=0 Then ts.FoutStockPlace else ts.FStockPlace end )
Left Join t_Stock tko On tko.FItemID=ts.FOutStockID
Left Join t_StockPlace tpo On tpo.FSPID=ts.FOutStockPlace
Where ts.FSerialClassID=1

[解决办法]

SQL code

SELECT ts.FSerialNum AS FSerialNum , tk.FName AS FStockName , t.FNumber AS FItemnumber , t.FName AS FItemName , ta.FName AS FAuxPropName , t.FModel , '预入库' AS FStatus , tk.FNumber AS FStockNumber , '仓库调拨' AS FLasttranType --,1 As FSumSort,1 AS FOrderFROM ICSerial ts INNER JOIN ICStockBill tb ON tb.FInterID = ts.FInterID INNER JOIN ICSerial_F1 ts1 ON ts1.FSerialClassID = ts.FSerialClassID AND ts1.FSerialID = ts.FSerialID INNER JOIN t_ICItem t ON t.FItemID = ts.FItemID LEFT JOIN t_AuxItem ta ON ta.FItemID = ts.FAuxPropID LEFT JOIN t_Stock tk ON tk.FItemID = ts.FStockID LEFT JOIN t_StockPlace tp ON tp.FSPID = ts.FStockPlace LEFT JOIN t_StockPlace tpo ON tpo.FSPID = ts.FOutStockPlaceWHERE ts.FSerialClassID = 1 AND ts.FTrantypeID = 41 AND ts.FValid = 0/*这部分没什么好说的,链接字段建好索引*/ UNION ALLSELECT ts.FSerialNum AS FSerialNum , tk.FName AS FStockName , t.FNumber AS FItemnumber , t.FName AS FItemName , ta.FName AS FAuxPropName , t.FModel , CASE WHEN ts.FTranTypeID IN ( 1, 2, 5, 10, 40 ) AND tb.FRob = 1 AND ts.FValid = 0 THEN '预入库' WHEN ts.FTranTypeID IN ( 1, 2, 5, 10, 40 ) AND tb.FRob = 1 AND ts.FValid = 1 THEN '在库' WHEN ts.FTranTypeID IN ( 1, 2, 5, 10, 40 ) AND tb.FRob = -1 AND ts.FValid = 0 THEN '预退库' WHEN ts.FTranTypeID IN ( 1, 2, 5, 10, 40 ) AND tb.FRob = -1 AND ts.FValid = 1 THEN '退库' WHEN ts.FTranTypeID IN ( 21, 24, 28, 29, 43 ) AND tb.FRob = -1 AND ts.FValid = 0 THEN '预入库' WHEN ts.FTranTypeID IN ( 21, 24, 28, 29, 43 ) AND tb.FRob = -1 AND ts.FValid = 1 THEN '在库' WHEN ts.FTranTypeID IN ( 21, 24, 28, 29, 43 ) AND tb.FRob = 1 AND ts.FValid = 0 THEN '预出库' WHEN ts.FTranTypeID IN ( 21, 24, 28, 29, 43 ) AND tb.FRob = 1 AND ts.FValid = 1 THEN '出库' WHEN ts.FTranTypeID = 41 AND ts.FValid = 1 THEN '在库' WHEN ts.FTranTypeID = 41 AND ts.FValid = 0 THEN '预出库' END AS FStatus , tk.FNumber AS FStockNumber , CASE WHEN ts.FTranTypeID = 1 THEN '外购入库' WHEN ts.FTranTypeID = 2 THEN '产品入库' WHEN ts.FTranTypeID = 5 THEN '委外加工入库' WHEN ts.FTranTypeID = 10 THEN '其他入库' WHEN ts.FTranTypeID = 21 THEN '销售出库' WHEN ts.FTranTypeID = 24 THEN '生产领料' WHEN ts.FTranTypeID = 28 THEN '委外加工发出' WHEN ts.FTranTypeID = 29 THEN '其他出库' WHEN ts.FTranTypeID = 40 THEN '盘盈入库' WHEN ts.FTranTypeID = 41 THEN '仓库调拨' WHEN ts.FTranTypeID = 43 THEN '盘亏毁损' WHEN ts.FTranTypeID = -1 THEN '初始化' END AS FLasttranType --,1 As FSumSort,0 AS FOrderFROM ICSerial ts INNER JOIN ICStockBill tb ON tb.FInterID = ts.FInterID INNER JOIN ICSerial_F1 ts1 ON ts1.FSerialClassID = ts.FSerialClassID AND ts1.FSerialID = ts.FSerialID INNER JOIN t_ICItem t ON t.FItemID = ts.FItemID LEFT JOIN t_AuxItem ta ON ta.FItemID = ts.FAuxPropID LEFT JOIN t_Stock tk ON tk.FItemID = ( CASE WHEN ts.FTranTypeID = 41 AND ts.FValid = 0 THEN ts.foutstockid ELSE ts.FStockID END ) LEFT JOIN t_StockPlace tp ON tp.FSPID = ( CASE WHEN ts.FTranTypeID = 41 AND ts.FValid = 0 THEN ts.FoutStockPlace ELSE ts.FStockPlace END ) LEFT JOIN t_StockPlace tpo ON tpo.FSPID = ts.FOutStockPlaceWHERE ts.FSerialClassID = 1/*case when 判断貌似可以调整一下，建议建立一张入库类型表1 外购入库 2 产品入库5 委外加工入库用链接的方式进行判断处理当然链接相关字段也还是要做好索引的*/UNION ALLSELECT ts.FSerialNum AS FSerialNum , tk.FName AS FStockName , t.FNumber AS FItemnumber , t.FName AS FItemName , ta.FName AS FAuxPropName , t.FModel , CASE ts.FValid WHEN 1 THEN '在库' ELSE '预入库' END AS FStatus , tk.FNumber AS FStockNumber , '初始化' AS FLasttranType --,1 As FSumSort,0 AS FOrderFROM ICSerial ts INNER JOIN IcInvInitial tb ON tb.FSNListID = ts.FSNListID INNER JOIN ICSerial_F1 ts1 ON ts1.FSerialClassID = ts.FSerialClassID AND ts1.FSerialID = ts.FSerialID INNER JOIN t_ICItem t ON t.FItemID = ts.FItemID LEFT JOIN t_AuxItem ta ON ta.FItemID = ts.FAuxPropID LEFT JOIN t_Stock tk ON tk.FItemID = ( CASE WHEN ts.FTranTypeID = 41 AND ts.FValid = 0 THEN ts.foutstockid ELSE ts.FStockID END ) LEFT JOIN t_StockPlace tp ON tp.FSPID = ( CASE WHEN ts.FTranTypeID = 41 AND ts.FValid = 0 THEN ts.FoutStockPlace ELSE ts.FStockPlace END ) LEFT JOIN t_Stock tko ON tko.FItemID = ts.FOutStockID LEFT JOIN t_StockPlace tpo ON tpo.FSPID = ts.FOutStockPlaceWHERE ts.FSerialClassID = 1/*同上 case when 的代替方法*/--另外，ctrl+L 查看是否走索引

[解决办法]
LZ那就得考虑如何降低此处的消耗了。如果筛选度高，就建个包含列的非聚集。如果筛选度不高，就看看碎片等等。

热点排行

SQL Server

帮忙优化一条MSSQL查询语句,该如何处理