I have query, but the response is very slowly. Where the wrong ? Please give some optimised solutions Thx declare @DSOLocalID VARCHAR(3), @Date SMALLDATETIME , @Stock VARCHAR(1) set @Date = convert(smalldatetime,'09-06-2011',103) set @DSOLocalID = '200' set @Stock = '' --=================================================== CREATE TABLE [dbo].[#Stocks]( [DSOLocalID] [varchar](3) NOT NULL, --[StockTransID] [varchar](10) NOT NULL, --[StockTransItemID] [varchar](3) NOT NULL, --[PostingDate] [smalldatetime] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [RokokID] [varchar](10) NOT NULL, [PitaCukai] [varchar](10) NOT NULL, [GudangID] [varchar](3) NOT NULL, [TransTypeID] [varchar](4) NOT NULL, [Calculate] [int] NOT NULL, --[TotalQty] [float] NOT NULL, --[TotalSatuan] [varchar](10) NOT NULL, [StockQtyBTG] [bigint] NOT NULL, [BonRokokID] [varchar](10) NULL, --[BonRokokItemID] [varchar](3) NULL, [GoodsTransactionID] [varchar](10) NULL, --[GoodsTransactionItemID] [varchar](3) NULL, [Status] [varchar](10) NOT NULL, --[TStamp] [timestamp] NOT NULL, --[Created] [smalldatetime] NULL, --[CreatedBy] [varchar](50) NULL, --[LastModified] [smalldatetime] NULL, --[LastModifiedBy] [varchar](50) NULL ) ON [PRIMARY] --CREATE CLUSTERED INDEX IX_RPT1 ON #Stocks (TransTypeID, TransDate, RokokID, PitaCukai, GudangID, Calculate, StockQtyBTG); CREATE NONCLUSTERED INDEX IX_RPT2 ON #Stocks (TransTypeID); CREATE NONCLUSTERED INDEX IX_RPT3 ON #Stocks (Calculate); CREATE NONCLUSTERED INDEX IX_RPT4 ON #Stocks (TransDate); --CREATE NONCLUSTERED INDEX IX_RPT4 ON #Stocks (RokokID, PitaCukai, GudangID, Calculate, StockQtyBTG); INSERT [dbo].[#Stocks](DSOLocalID, TransDate, RokokID, PitaCukai, GudangID, TransTypeID, Calculate, StockQtyBTG, BonRokokID, GoodsTransactionID, Status) SELECT DSOLocalID, TransDate, RokokID, PitaCukai, GudangID, TransTypeID, Calculate, StockQtyBTG, BonRokokID, GoodsTransactionID, Status FROM tblSATStockTrans S (READUNCOMMITTED) WHERE S.DSOLocalID = @DSOLocalID AND S.[Status] = 'POSTING' ;with tempStockTrans (RokokID, PitaCukai, GudangID, Calculate, StockQtyBTG ) AS ( --Transaksi stok sesudah parameter tanggal, tidak termasuk transaksi outstanding stok --> NOT IN ('6500', '6501') StockTransQtyBTG = S.Calculate * S.StockQtyBTG SELECT S.RokokID, S.PitaCukai, S.GudangID, S.Calculate, S.StockQtyBTG FROM [#Stocks] S LEFT JOIN tblSATBonRokok B (READUNCOMMITTED) ON S.BonRokokID = B.BonRokokID AND S.DSOLocalID = B.DSOLocalID LEFT JOIN tblSATGoodsTransaction G (READUNCOMMITTED) ON S.GoodsTransactionID = G.GoodsTransactionID AND S.DSOLocalID = G.DSOLocalID WHERE (S.TransTypeID <> '6500') AND (S.TransTypeID <> '6501') AND ((ISNULL(B.BonRokokID, '') = '' AND (ISNULL(G.GoodsTransactionID, '') = '') AND CONVERT(CHAR(10), S.TransDate, 112) > CONVERT(CHAR(10), @Date, 112)) OR (ISNULL(B.BonRokokID, '') <> '' AND ((S.Calculate = -1 AND CONVERT(CHAR(10), B.OpenTransDate, 112) > CONVERT(CHAR(10), @Date, 112)) OR (S.Calculate = 1 AND CONVERT(CHAR(10), B.CloseTransDate, 112) > CONVERT(CHAR(10), @Date, 112)))) OR (ISNULL(G.GoodsTransactionID, '') <> '' AND ((S.Calculate = -1 AND CONVERT(CHAR(10), G.TglKirim, 112) > CONVERT(CHAR(10), @Date, 112)) OR (S.Calculate = 1 AND CONVERT(CHAR(10), G.TglTerima, 112) > CONVERT(CHAR(10), @Date, 112))))) ) SELECT RokokID, PitaCukai, GudangID, Calculate, StockQtyBTG from tempStockTrans DROP TABLE [#Stocks]
Hi Luis Martin, I was check execution plan, table [#Stocks] use table scan. ;with tempStockTrans (RokokID, PitaCukai, GudangID, Calculate, StockQtyBTG ) AS ( --Transaksi stok sesudah parameter tanggal, tidak termasuk transaksi outstanding stok --> NOT IN ('6500', '6501') StockTransQtyBTG = S.Calculate * S.StockQtyBTG SELECT S.RokokID, S.PitaCukai, S.GudangID, S.Calculate, S.StockQtyBTG FROM [#Stocks] S LEFT JOIN tblSATBonRokok B (READUNCOMMITTED) ON S.BonRokokID = B.BonRokokID AND S.DSOLocalID = B.DSOLocalID LEFT JOIN tblSATGoodsTransaction G (READUNCOMMITTED) ON S.GoodsTransactionID = G.GoodsTransactionID AND S.DSOLocalID = G.DSOLocalID WHERE (S.TransTypeID <> '6500') AND (S.TransTypeID <> '6501') AND ((ISNULL(B.BonRokokID, '') = '' AND (ISNULL(G.GoodsTransactionID, '') = '') AND CONVERT(CHAR(10), S.TransDate, 112) > CONVERT(CHAR(10), @Date, 112)) OR (ISNULL(B.BonRokokID, '') <> '' AND ((S.Calculate = -1 AND CONVERT(CHAR(10), B.OpenTransDate, 112) > CONVERT(CHAR(10), @Date, 112)) OR (S.Calculate = 1 AND CONVERT(CHAR(10), B.CloseTransDate, 112) > CONVERT(CHAR(10), @Date, 112)))) OR (ISNULL(G.GoodsTransactionID, '') <> '' AND ((S.Calculate = -1 AND CONVERT(CHAR(10), G.TglKirim, 112) > CONVERT(CHAR(10), @Date, 112)) OR (S.Calculate = 1 AND CONVERT(CHAR(10), G.TglTerima, 112) > CONVERT(CHAR(10), @Date, 112))))) ) thx