100万条纪录查询超级的慢
ICStockBillEntry u1 出入库详细分录表 这个是主表,ftrantype=1为外购入库,大约有100万条记录,虽然使用了where fdate between 限制时间,但没啥效果查询还是超级的慢!!! 求解决办法!!!
icstockbill v1 出入库表 Select v1.FInterID AS FInterID,u1.FEntryID AS FEntryID,v1.Fdate AS Fdate,case when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end AS FCheck,case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,v1.FBillNo AS FBillNo,CASE WHEN v1.FSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FSCBillNo,CASE WHEN v1.FRSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FRSCBillNo,CASE WHEN v1.FBSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FBSCBillNo,CASE WHEN v1.FOrderInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FOrderBillNo,t4.FName AS FSupplyIDName,t7.FName AS FDCStockIDName,t13.FShortNumber AS FItemIDName,t13.FNumber AS FFullNumber,t13.Fname AS FItemName,t13.Fmodel AS FItemModel,t17.FName AS FUnitIDName,u1.FBatchNo AS FBatchNo,u1.Fauxprice AS Fauxprice,u1.FAuxQtyMust AS FAuxQtyMust,u1.Fauxqty AS Fauxqty,u1.Famount AS Famount,u1.FNote AS FNote,t10.FName AS FuserName,t9.FName AS FSManagerIDName,t8.FName AS FFManagerIDName,t24.FName AS FCheckerName,CASE WHEN v1.FQCRBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FQCRBillNo,case when v1.FVchInterID>0 then 'Y' when v1.FVchInterID<0 then 'Y' else '' end AS FVoucherStatus,(SELECT (SELECT FName FROM t_VoucherGroup WHERE FGroupID=t_Voucher.FGroupID)+'-'+CONVERT(Varchar(30),FNumber) FROM t_Voucher WHERE FVoucherid=v1.FVchInterID) AS FVoucherNumber,case when v1.FHookInterID>0 then 'Y' when v1.FHookInterID<0 then 'Y' else '' end AS FHookStatus,CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FINBillInterID = v1.FInterID AND FTranType = 24) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ZPStockBill WHERE FRelateBillInterID = v1.FInterID AND FTranType = 6) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICPurchase WHERE FInStockInterID = v1.FInterID AND FTranType = 76) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICPurchase WHERE FInStockInterID = v1.FInterID AND FTranType = 75) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FINSCBillInterID = v1.FInterID AND FTranType = 21) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FRSCBillInterID = v1.FInterID AND FTranType = 101) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FRSCBillInterID = v1.FInterID AND FTranType = 1) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FQCRBillInterID = v1.FInterID AND FTranType = 702) THEN 'Y' ELSE '' END END END END END END END END AS FReStatus,case when (v1.FOrgBillInterID <> 0) then 'Y' else null end AS FHasSplitBill,t30.FName AS FBaseUnitID,u1.FQtyMust AS FBaseQtyMust,u1.FQty AS FBaseQty,u1.FAuxPlanPrice AS FAuxPlanPrice,u1.FPlanAmount AS FPlanAmount,Case WHEN t13.FCUUnitID=0 THEN '' Else t500.FName end AS FCUUnitName,Case WHEN t13.FCUUnitID=0 THEN '' Else u1.FQtyMust/t500.FCoefficient end AS FCUUnitQtyMust,Case WHEN t13.FCUUnitID=0 THEN '' Else u1.FQty/t500.FCoefficient end AS FCUUnitQty,t510.FName AS FSPName,u1.FKFPeriod AS FKFPeriod,u1.FKFDate AS FKFDate,CASE WHEN v1.FInvoiceInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FPurchaseBillNo,t6.FName AS FPOStyleName,t523.FBillNo AS FZPBillNo,u1.FMapName AS FMapName,u1.FMapNumber AS FMapNumber,t550.FName AS FRelateBrIDName,t7.FNumber AS FDCStockIDNumber,u1.FEntrySelfA0127 AS FEntrySelfA0127,u1.FEntrySelfA0128 AS FEntrySelfA0128,u1.FEntrySelfA0129 AS FEntrySelfA0129,u1.FEntrySelfA0130 AS FEntrySelfA0130,u1.fitemid AS fitemid from ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
Inner Join t_Supplier t4 on v1.FSupplyID=t4.FItemID
left outer join POInStock t5 on v1.FSCBillInterID=t5.FInterID
Inner Join t_Stock t7 on v1.FDCStockID=t7.FItemID
left outer join t_Emp t8 on v1.FFManagerID=t8.FItemID
left outer join t_Emp t9 on v1.FSManagerID=t9.FItemID
Inner Join t_User t10 on v1.FBillerID=t10.FUserID
left outer join ICStockBill t11 on v1.FRSCBillInterID=t11.FInterID
left outer join POInStock t12 on v1.FBSCBillInterID=t12.FInterID
Inner Join t_ICItem t13 on u1.FItemID=t13.FItemID
Inner Join t_MeasureUnit t16 on t13.FUnitID=t16.FItemID
Inner Join t_MeasureUnit t17 on u1.FUnitID=t17.FItemID
left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
Inner Join t_MeasureUnit t30 on t13.FUnitID=t30.FItemID
left outer join POOrder t31 on v1.FOrderInterID=t31.FInterID
left outer join t_MeasureUnit t500 on t13.FCUUnitID=t500.FItemID
left outer join t_Currency t503 on v1.FCurrencyID=t503.FCurrencyID
left outer join t_StockPlace t510 on u1.FDCSPID=t510.FSPID
left outer join t_SubMessage t6 on v1.FPOStyle=t6.FInterID
left outer join ICPurchase t100 on v1.FInvoiceInterID=t100.FInterID
left outer join ZPStockBill t523 on v1.FInterID=t523.FRelateBillInterID
left outer join t_SonCompany t550 on v1.FRelateBrID=t550.FItemID where
v1.FTranType=1 and v1.fdate between '2008-08-01' and '2008-08-15'
ICStockBillEntry u1 出入库详细分录表 这个是主表,ftrantype=1为外购入库,大约有100万条记录,虽然使用了where fdate between 限制时间,但没啥效果查询还是超级的慢!!! 求解决办法!!!
icstockbill v1 出入库表 Select v1.FInterID AS FInterID,u1.FEntryID AS FEntryID,v1.Fdate AS Fdate,case when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end AS FCheck,case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,v1.FBillNo AS FBillNo,CASE WHEN v1.FSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FSCBillNo,CASE WHEN v1.FRSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FRSCBillNo,CASE WHEN v1.FBSCBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FBSCBillNo,CASE WHEN v1.FOrderInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FOrderBillNo,t4.FName AS FSupplyIDName,t7.FName AS FDCStockIDName,t13.FShortNumber AS FItemIDName,t13.FNumber AS FFullNumber,t13.Fname AS FItemName,t13.Fmodel AS FItemModel,t17.FName AS FUnitIDName,u1.FBatchNo AS FBatchNo,u1.Fauxprice AS Fauxprice,u1.FAuxQtyMust AS FAuxQtyMust,u1.Fauxqty AS Fauxqty,u1.Famount AS Famount,u1.FNote AS FNote,t10.FName AS FuserName,t9.FName AS FSManagerIDName,t8.FName AS FFManagerIDName,t24.FName AS FCheckerName,CASE WHEN v1.FQCRBillInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FQCRBillNo,case when v1.FVchInterID>0 then 'Y' when v1.FVchInterID<0 then 'Y' else '' end AS FVoucherStatus,(SELECT (SELECT FName FROM t_VoucherGroup WHERE FGroupID=t_Voucher.FGroupID)+'-'+CONVERT(Varchar(30),FNumber) FROM t_Voucher WHERE FVoucherid=v1.FVchInterID) AS FVoucherNumber,case when v1.FHookInterID>0 then 'Y' when v1.FHookInterID<0 then 'Y' else '' end AS FHookStatus,CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FINBillInterID = v1.FInterID AND FTranType = 24) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ZPStockBill WHERE FRelateBillInterID = v1.FInterID AND FTranType = 6) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICPurchase WHERE FInStockInterID = v1.FInterID AND FTranType = 76) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICPurchase WHERE FInStockInterID = v1.FInterID AND FTranType = 75) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FINSCBillInterID = v1.FInterID AND FTranType = 21) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FRSCBillInterID = v1.FInterID AND FTranType = 101) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FRSCBillInterID = v1.FInterID AND FTranType = 1) THEN 'Y' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FQCRBillInterID = v1.FInterID AND FTranType = 702) THEN 'Y' ELSE '' END END END END END END END END AS FReStatus,case when (v1.FOrgBillInterID <> 0) then 'Y' else null end AS FHasSplitBill,t30.FName AS FBaseUnitID,u1.FQtyMust AS FBaseQtyMust,u1.FQty AS FBaseQty,u1.FAuxPlanPrice AS FAuxPlanPrice,u1.FPlanAmount AS FPlanAmount,Case WHEN t13.FCUUnitID=0 THEN '' Else t500.FName end AS FCUUnitName,Case WHEN t13.FCUUnitID=0 THEN '' Else u1.FQtyMust/t500.FCoefficient end AS FCUUnitQtyMust,Case WHEN t13.FCUUnitID=0 THEN '' Else u1.FQty/t500.FCoefficient end AS FCUUnitQty,t510.FName AS FSPName,u1.FKFPeriod AS FKFPeriod,u1.FKFDate AS FKFDate,CASE WHEN v1.FInvoiceInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS FPurchaseBillNo,t6.FName AS FPOStyleName,t523.FBillNo AS FZPBillNo,u1.FMapName AS FMapName,u1.FMapNumber AS FMapNumber,t550.FName AS FRelateBrIDName,t7.FNumber AS FDCStockIDNumber,u1.FEntrySelfA0127 AS FEntrySelfA0127,u1.FEntrySelfA0128 AS FEntrySelfA0128,u1.FEntrySelfA0129 AS FEntrySelfA0129,u1.FEntrySelfA0130 AS FEntrySelfA0130,u1.fitemid AS fitemid from ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
Inner Join t_Supplier t4 on v1.FSupplyID=t4.FItemID
left outer join POInStock t5 on v1.FSCBillInterID=t5.FInterID
Inner Join t_Stock t7 on v1.FDCStockID=t7.FItemID
left outer join t_Emp t8 on v1.FFManagerID=t8.FItemID
left outer join t_Emp t9 on v1.FSManagerID=t9.FItemID
Inner Join t_User t10 on v1.FBillerID=t10.FUserID
left outer join ICStockBill t11 on v1.FRSCBillInterID=t11.FInterID
left outer join POInStock t12 on v1.FBSCBillInterID=t12.FInterID
Inner Join t_ICItem t13 on u1.FItemID=t13.FItemID
Inner Join t_MeasureUnit t16 on t13.FUnitID=t16.FItemID
Inner Join t_MeasureUnit t17 on u1.FUnitID=t17.FItemID
left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
Inner Join t_MeasureUnit t30 on t13.FUnitID=t30.FItemID
left outer join POOrder t31 on v1.FOrderInterID=t31.FInterID
left outer join t_MeasureUnit t500 on t13.FCUUnitID=t500.FItemID
left outer join t_Currency t503 on v1.FCurrencyID=t503.FCurrencyID
left outer join t_StockPlace t510 on u1.FDCSPID=t510.FSPID
left outer join t_SubMessage t6 on v1.FPOStyle=t6.FInterID
left outer join ICPurchase t100 on v1.FInvoiceInterID=t100.FInterID
left outer join ZPStockBill t523 on v1.FInterID=t523.FRelateBillInterID
left outer join t_SonCompany t550 on v1.FRelateBrID=t550.FItemID where
v1.FTranType=1 and v1.fdate between '2008-08-01' and '2008-08-15'
你那么多case when，估计电脑也算得出汗了。
按逻辑分开写吧，这样维护起来也容易些。
1、优化索引
2、优化SQL语句，可以通过优化工具LECCO_SQL来实现