SQL Server Performance

Performance monitor

Discussion in 'SQL Server 2005 General DBA Questions' started by iCool, May 10, 2011.

  1. iCool New Member

    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]
  2. Luis Martin Moderator

    Welcome to the forums!
    Did you check execution plan?
  3. iCool New Member

    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
  4. shabnyc Member

    Best thing to do is Create table(s), Insert Data, then create the indexes.

Share This Page