May 10, 2011 at 3:33 am
i have query :
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)
SELECTDSOLocalID, TransDate, RokokID, PitaCukai, GudangID, TransTypeID,
Calculate, StockQtyBTG, BonRokokID, GoodsTransactionID, Status
FROMtblSATStockTrans S (READUNCOMMITTED)
WHERES.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]
The response very slowly, where the wrong from my query?
If look in execution plan use scan table for#Stocks.
Please give some optimised solutions
Thx
May 10, 2011 at 9:54 am
You might want to restate your question and provide additional details.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 9:24 pm
hi Welsh Corgi,
i need to optimised query
***********************************
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)))))
**************************************************
I was check in execution plan, still use table scan ([#Stocks]). Why?
Thx
May 10, 2011 at 9:38 pm
You are doing a number of things to render the indexes useless. The <> will tend to force SQL to consider a table scan because it has to look at a lot of records to determine if they are not equal to those values.
Also all the functions in the WHERE clause mean the index often cannot be used. When you do the type conversions, the indexes don't satisfy anymore and so a table scan becomes more effective.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply