SQL Server Performance

Index scan

Discussion in 'SQL Server 2008 General DBA Questions' started by darkangelBDF, Aug 5, 2009.

  1. darkangelBDF Member

    Hi there all. Trust you're all doing well.
    I have about 47 odd million rows that I have to migrate from one table to another. I use the code below but when I run the show query execution plan I see that it does an index scan on one of the non-clustered indexes. That takes up about 12% of the task. Then there's an index seek on the clustered index which takes around 95%. My question is this. Is there any way I can improve the code below? Basically there are currently 239000000 odd rows in the Fact table. Only 47000000 odd needs to over to the dbo Migration table. The records in the Fact table can be discarded, so that is not an issue. How can I improve this query to do just that without it taking me years to run? I'm still very new to the whole writing-more-complex-queries thing.USE
    NOCOUNT ON;--Declaration of configurable variables (requires specification of values below)DECLARE
    @BatchSize int;DECLARE
    @BatchLoopDelay_InMilliseconds int;DECLARE
    @SLSPRowCount int;DECLARE
    @IteratorSeqNo int;DECLARE
    @BatchLoopDelay_InTimeFormat char(12);--Setting the values of configurable variablesSELECT
    @BatchSize = 100000;SELECT
    @BatchLoopDelay_InMilliseconds = 150;--Get the minimum StoreID (which will be the first store to be migrated).SET
    @IteratorSeqNo = 1;--Convert the specified millisecond delays into a format that can later be used by the WAITFOR DELAY command.SELECT
    @BatchLoopDelay_InTimeFormat = CONVERT(char(12), DATEADD(millisecond, @BatchLoopDelay_InMilliseconds, '00:00:00.000'), 114);WHILE
    --Delete rows from Fact tableDELETE
    TOP(@BatchSize) [Fact].[StockLedgerSalePriceTEMPORARY]--Insert output from delete statement into temporary tableOUTPUTDeleted.[SK_Store], Deleted
    .[SK_Stock], Deleted.[SK_TransactionType], Deleted
    .[TransactionID], Deleted
    .[MarkDown],Deleted.[PriceOverride], Deleted
    .[PosDiscount], Deleted
    [Fact].[StockLedgerSalePriceTEMPORARY] AS dSLSPJOIN
    mig.StockLedger AS TSPONdSLSP
    .[SK_Store] = TSP.[SK_Store] ANDdSLSP
    .[SK_Stock] = TSP.[SK_Stock] ANDdSLSP
    .[SK_TransactionType] = TSP.[SK_TransactionType] ANDdSLSP
    .[TransactionID] = TSP.[TransactionID];SELECT
    @SLSPRowCount = @@ROWCOUNT;IF
    (@SLSPRowCount = 0)
    DELAY @BatchLoopDelay_InTimeFormat;
  2. preethi Member

    Can you please publish the table structures, Indexes and relationships.
    You can think of insert fist and then truncate the whole table method.
  3. darkangelBDF Member

    I'll try and give you the info best I can. In this situation there are three tables: Fact.StockLedgerSalePriceTEMPORARY, mig.StockLedgerSalePrice and mig.StockLedger.
    Fact.StockLedgerSalePriceTEMPORARY contains the records that need to be 'copied' over to mig.StockLedgerSalePrice. We're joining it to mig.StockLedger because it's the only table that contains the GLPeriodID field which we desperately need to align things.Fact.StockLedgerSalePriceTEMPORARYColumnsSK_Store (PK, int, NOT NULL)SK_Stock (PK, int, NOT NULL)SK_TransactionType (PK, int, NOT NULL)TransactionID (PK, int, NOT NULL)PromoDiscount (money, NOT NULL)MarkDown (money, NOT NULL)
    PriceOverride (money, NOT NULL)PosDiscount (money, NULL)PosPromoDiscount (money, NULL)PosMarkDown (money, NULL)PosPriceOverride (money, NULL)SellPrice (money, NULL)CurrentPrice (money, NULL) Indexes Primary Key PK_StockLedgerSalePrice (Clustered) referencing SK_Store, SK_Stock, SK_TransactionType, TransactionIDmig.StockLedgerSalePrice Columns SK_Store (PK, int, NOT NULL)SK_Stock (PK, int, NOT NULL)
    SK_TransactionType (PK, int, NOT NULL)TransactionID (PK, int, NOT NULL)PromoDiscount (money, NOT NULL)MarkDown (money, NOT NULL)PriceOverride (money, NOT NULL)PosDiscount (money, NULL)PosPromoDiscount (money, NULL)
    PosMarkDown (money, NULL)PosPriceOverride (money, NULL)SellPrice (money, NULL)CurrentPrice (money, NULL)Indexes Primary Key PK_StockLedgerSalePrice (Clustered Primary Key) referencing SK_Store, SK_Stock, SK_TransactionType, TransactionID, GLPeriod (we get this value from mig.StockLedger). This table is a partitioned table. mig.StockLedger Columns SK_Store (PK, int, NOT NULL)SK_Stock (PK, int, NOT NULL)SK_TransactionType (PK, int, NOT NULL)SK_Calendar_DocketDate (PK, int, NOT NULL)SK_Calendar_TradingDate (PK, int, NOT NULL)TransactionID (PK, int, NOT NULL)LedgerQty (real, not null)DocketCode (char(20), not null)AverageCostPrice (money, not null)SellPrice (money, not null)CurrentPrice (money, null)LedgerID (bigint, null)StockPeriodID (int, not null)GLPeriodID (PK, int, not null) Indexes IX_NC_StockLedger_LedgerID (non-clustered, non-unique) references LedgerIDPK_StockLedger (clustered Primary Key) references SK_Store, SK_Stock, SK_TransactionType, SK_Calendar_DocketDate, SK_Calendar_TradingDate, TransactionID, GLPeriodID
  4. preethi Member

    Try this method: It will perform better. But you need to decide whether it is optimum.
    Get the minimum SK_Stock from Temp table into a variable @SK_Stock.
    While @SK_Stock IS NOT NULL
    do the insert (by joining to the other table) where SK_Stock = @SK_Stock
    Get the next SK_Stock into @SK_Stock
    truncate temp table.
    Hope this helps.

Share This Page