SQL Server Performance

need help to improve this query....

Discussion in 'T-SQL Performance Tuning for Developers' started by tran008, Jan 6, 2009.

  1. tran008 New Member

    Hi all,
    I have the code below, which used for stock analysis base on the first in first out. As you can see if need a lot of help....the currently code is to slow, because of the while loop. I been struggling with this, and need some new input on how to rewrite these code. Any help to improve this thing is greatly appreciated.
    DECLARE @OrderTable TABLE(
    ROWID INT IDENTITY ( 1,1 ),
    ID INT,
    ACCT INT,
    BUYDATE DATETIME,
    SELLDATE DATETIME,
    B_QTY INT,
    B_COST MONEY,
    S_QTY INT,
    S_COST MONEY
    )

    DECLARE @t1 TABLE(
    [ID] INTEGER IDENTITY ( 1,1 ),
    ACCT INT,
    XDATE DATETIME,
    XCODE CHAR(1),
    QTY INT,
    COST MONEY
    )

    INSERT @t1 VALUES('100101','2003-10-2','B',100,50)
    INSERT @t1 VALUES('100101','2003-10-2','B',200,45)
    INSERT @t1 VALUES('100101','2003-10-1','B',300,35)
    INSERT @t1 VALUES('100101','2003-10-1','S',400,55)
    INSERT @t1 VALUES('100101','2003-10-2','S',200,20)
    INSERT @t1 VALUES('100102','2003-10-2','B',500,12)
    INSERT @t1 VALUES('100102','2003-10-3','S',800,13)
    INSERT @t1 VALUES('100102','2003-10-3','B',500,11)
    INSERT @t1 VALUES('100103','2003-10-4','B',100,12)
    INSERT @t1 VALUES('100103','2003-10-5','B',200,12)
    INSERT @t1 VALUES('100103','2003-10-6','S',500,15)

    SELECT * FROM @T1

    DECLARE @xCountMax INT,
    @xCount INT,
    @id_in INT,
    @id_out INT,
    @date1 DATETIME,
    @date2 DATETIME,
    @qtyin INT,
    @qtyout INT,
    @qtyin2 INT,
    @qtyout2 INT,
    @costin MONEY,
    @costout MONEY,
    @inClmid INT,
    @outClmid INT,
    @flagid INT,
    @flagout INT,
    @acct INT

    SET @xCount = 1

    IF OBJECT_ID('tempdb..#TMPCLMID','U') IS NOT NULL
    DROP TABLE #TMPCLMID

    SELECT IDENTITY(INT,1,1) AS ROWID,
    ACCT
    INTO #TMPCLMID
    FROM @t1
    GROUP BY ACCT

    SET @xCount = 1

    SET @xCountMax = (SELECT MAX(ROWID)
    FROM #TMPCLMID)

    WHILE @xCount <= @xCountMax
    BEGIN

    IF OBJECT_ID('tempdb..#buy','U') IS NOT NULL
    DROP TABLE #BUY

    IF OBJECT_ID('tempdb..#sell','U') IS NOT NULL
    DROP TABLE #SELL

    SELECT @ACCT = ACCT

    FROM #TMPCLMID
    WHERE ROWID = @xCount

    SELECT IDENTITY(INT,0,1) AS ROWID,
    ACCT,
    XDATE,
    XCODE,
    QTY,
    COST
    INTO #BUY
    FROM @t1
    WHERE XCODE = 'B'
    AND ACCT = @ACCT

    SELECT IDENTITY(INT,0,1) AS ROWID,
    ACCT,
    XDATE,
    XCODE,
    QTY,
    COST
    INTO #SELL
    FROM @t1
    WHERE XCODE = 'S'
    AND ACCT = @ACCT

    SET @flagid = 0
    SET @flagout = 0
    SET @qtyout = 1
    SET @qtyin = 1
    SET @qtyout2 = 1
    SET @qtyin2 = 1

    WHILE (@qtyout <> 0
    AND @qtyin <> 0)
    BEGIN
    IF @flagid = 0
    BEGIN
    SET @qtyout = 0

    SET @qtyin = 0

    SELECT @id_in = ROWID,
    @date1 = XDATE,
    @qtyin = QTY,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = 0

    SELECT @id_out = ROWID,
    @date2 = XDATE,
    @qtyout = ISNULL(QTY,0),
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = 0

    IF ISNULL(@qtyin,0) = 0
    BEGIN
    SET @id_in = @id_in + 1

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    SELECT @id_in = ROWID,
    @date1 = XDATE,
    @qtyin = QTY,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_in
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0
    END
    END

    IF ISNULL(@qtyout,0) = 0
    BEGIN
    SET @id_out = @id_out + 1

    IF EXISTS (SELECT 1
    FROM #SELL
    WHERE ROWID = @id_out)
    SELECT @id_out = ROWID,
    @date2 = XDATE,
    @qtyout = QTY,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @id_out
    ELSE
    BEGIN
    SET @qtyout = 0

    SET @qtyout2 = 0
    END
    END

    SET @flagid = @flagid + 1
    END

    IF (@qtyin >= @qtyout)
    BEGIN
    IF (@qtyout > 0)
    BEGIN
    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyout,
    @costin,
    @date2,
    @qtyout,
    @costout,
    @acct
    END
    ELSE
    BEGIN
    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyin,
    @costin,
    NULL,
    0,
    0,
    @acct

    SET @id_in = @id_in + 1

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    SELECT @qtyin = QTY,
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_in
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0
    END
    END

    SET @qtyin = @qtyin - @qtyout

    SET @id_out = @id_out

    SET @qtyout = @qtyout

    SET @qtyin = @qtyin

    SET @id_out = @id_out + 1

    IF EXISTS (SELECT 1
    FROM #SELL
    WHERE ROWID = @id_out)
    BEGIN
    SELECT @qtyout = ISNULL(QTY,0),
    @date2 = XDATE,
    @qtyout = QTY,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @id_out

    END
    ELSE
    BEGIN
    SET @qtyout = 0

    SET @qtyout2 = 0
    END

    SET @qtyout = @qtyout

    IF (@qtyin < @qtyout)

    BEGIN

    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyin,
    @costin,
    @date2,
    @qtyin,
    @costout,
    @acct

    SET @qtyout = ((@qtyout) - (@qtyin))
    SET @id_in = @id_in
    SET @id_in = @id_in + 1

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    SELECT @qtyin = ISNULL(QTY,0),
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_in
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0
    END
    END
    ELSE
    BEGIN
    WHILE (@qtyin >= @qtyout)
    BEGIN
    SET @qtyout = @qtyout

    IF @qtyout > 0
    BEGIN
    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyout,
    @costin,
    @date2,
    @qtyout,
    @costout,
    @acct
    END

    SET @qtyin = @qtyin - @qtyout

    SET @id_out = @id_out + 1

    IF @qtyin = 0
    BEGIN

    SET @id_in = @id_in + 1

    SET @qtyout = @qtyout

    IF EXISTS (SELECT 1
    FROM #SELL
    WHERE ROWID = @id_out)
    SELECT @QTYOUT = QTY,
    @date2 = XDATE,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @id_out
    ELSE
    BEGIN
    SET @qtyout = 0

    SET @qtyout2 = 0
    END

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    SELECT @QTYIN = QTY,
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_IN
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0
    END

    BREAK
    END

    IF EXISTS (SELECT 1
    FROM #SELL
    WHERE ROWID = @id_out)
    BEGIN
    SELECT @QTYOUT = QTY,
    @date2 = XDATE,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @id_out

    SET @qtyin = @qtyin
    END
    ELSE
    BEGIN
    SET @qtyout = 0

    SET @qtyin = ((@qtyin) - (@qtyout))

    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyin,
    @costin,
    NULL,
    0,
    0,
    @acct

    SET @id_in = @id_in

    SET @id_in = @id_in + 1

    SET @qtyout2 = 0

    BREAK
    END

    END
    END
    END

    ELSE
    BEGIN

    IF (@qtyin > 0)

    BEGIN

    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyin,
    @costin,
    @date2,
    @qtyin,
    @costout,
    @acct

    SET @id_in = @id_in + 1

    SET @qtyout = @qtyout - @qtyin

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    BEGIN
    SELECT @QTYIN = QTY,
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_in

    IF @qtyin > @qtyout2
    SET @flagout = 0
    END
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0
    END

    END
    ELSE
    BEGIN
    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    NULL,
    0,
    0,
    @date2,
    @qtyout,
    @costout,
    @acct

    SET @id_in = @id_in + 1

    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    BEGIN
    SELECT @QTYIN = QTY,
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @id_in

    IF @qtyin > @qtyout2
    SET @flagout = 0
    END
    ELSE
    BEGIN
    SET @qtyin = 0

    SET @qtyin2 = 0

    SET @ID_OUT = @ID_OUT + 1

    SELECT @qtyout = QTY,
    @date2 = XDATE,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @ID_OUT

    END

    END

    END

    IF @qtyout2 = 0

    BEGIN
    WHILE @qtyout2 <> 1
    BEGIN
    IF EXISTS (SELECT 1
    FROM #BUY
    WHERE ROWID = @id_in)
    BEGIN
    SELECT @qtyin = QTY,
    @date1 = XDATE,
    @costin = COST,
    @acct = ACCT
    FROM #BUY
    WHERE ROWID = @ID_IN

    SET @qtyin = @qtyin

    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    @date1,
    @qtyin,
    @costin,
    NULL,
    0,
    0,
    @acct

    SET @ID_IN = @ID_IN + 1
    END
    ELSE
    BEGIN
    SET @qtyin2 = 0

    SET @qtyin = 0

    BREAK
    END
    END

    END

    IF @qtyin2 = 0
    BEGIN

    WHILE @qtyin2 <> 1
    BEGIN
    IF EXISTS (SELECT 1
    FROM #SELL
    WHERE ROWID = @id_out)
    BEGIN
    INSERT INTO @OrderTable
    (ID,
    BUYDATE,
    B_QTY,
    B_COST,
    SELLDATE,
    S_QTY,
    S_COST,
    ACCT)
    SELECT @id_in,
    NULL,
    0,
    0,
    @date2,
    @qtyout,
    @costout,
    @acct

    SET @ID_OUT = @ID_OUT + 1

    SELECT @qtyout = QTY,
    @date2 = XDATE,
    @costout = COST,
    @acct = ACCT
    FROM #SELL
    WHERE ROWID = @ID_OUT
    END
    ELSE
    BEGIN
    SET @qtyout2 = 0

    SET @qtyout = 0

    BREAK
    END
    END
    END
    END

    SET @xCount = @xCount + 1

    END

    SELECT rowid,acct,buydate,selldate,b_qty,b_cost,s_qty,s_cost
    FROM @OrderTable
    ORDER BY ACCT

  2. satya Moderator

    Before that I would like to know/check the indexes for the columns that are specified within the WHERE clausing.
  3. tran008 New Member

    Hi Satya,
    the index is on the rowid. I beleived the while loop is the one causing problem. If individual account is processing, it took around 2 seconds, but when group with other accounts, the while loop is taking to much time.
    thanks

Share This Page