Professional Web Applications Themes

Transact SQL question - Microsoft SQL / MS SQL Server

Hello. I am trying to improve the speed & efficiency of some Transact-SQL code I'm writing, but I'm not sure if it's possible. I'm using SQL Server 7.0. Basically, I'm using a cursor to loop through a list of sorted records, then update another field in that record based on TotalVal and the maxval field. The sample code below works - I'm just trying to improve the speed & efficiency and maybe get rid of the cursor and use a single update statement, if possible. Any suggestions you have would be appreciated. Thanks. -------------------Sample Code----------------- declare idval int declare rank ...

  1. #1

    Default Transact SQL question

    Hello. I am trying to improve the speed & efficiency of some
    Transact-SQL code I'm writing, but I'm not sure if it's possible. I'm
    using SQL Server 7.0.

    Basically, I'm using a cursor to loop through a list of sorted
    records, then update another field in that record based on TotalVal
    and the maxval field. The sample code below works - I'm just trying to
    improve the speed & efficiency and maybe get rid of the cursor and use
    a single update statement, if possible.

    Any suggestions you have would be appreciated. Thanks.

    -------------------Sample Code-----------------
    declare idval int
    declare rank int
    declare maxval float
    declare quit_flag bit
    declare TotalVal float
    select TotalVal = 9.0 --this could be any value
    select quit_flag = 0

    declare curTemp cursor local fast_forward
    for select idval, rank, maxval from #temp1 order by rank

    open curTemp
    fetch next from curTemp into idval, rank, maxval

    while fetch_status = 0 and quit_flag = 0
    begin

    if TotalVal > maxval
    begin --val should not be set greater than maxval
    update #temp1 set val = maxval where idval = idval
    --save the remainder for the next ranked record
    set TotalVal = TotalVal - maxval
    end
    else
    begin --remainder of TotalVal can all be applied to this
    --record
    update #temp1 set val = TotalVal where idval = idval
    set TotalVal = 0
    set quit_flag = 1
    end

    fetch next from curTemp into idval, rank, maxval
    end

    close curTemp
    deallocate curTemp

    select * from #temp1 order by rank

    -------------Sample DDL Statements------------------
    create table #temp1 (idval int, rank int, maxval float, val float)
    --idval is unique for each record

    ----------Sample Insert Statements-----------------
    insert into #temp1 (idval, rank, maxval, val) values (1, 1, 1.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (22, 2, 3.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (3, 3, 2.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (6, 3, 10.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (24, 5, 1.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (8, 6, 5.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (15, 6, 6.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (9, 8, 1.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (17, 9, 4.0, 0)
    insert into #temp1 (idval, rank, maxval, val) values (15, 11, 2.0, 0)
    Peter Guest

  2. #2

    Default Re: Transact SQL question

    I've assumed that Idval is actually unique as you stated and that the final
    duplicate row (Idval=15) of sample data you posted was a mistake. I don't
    think you can do this without a PK. I've also assumed an upper bound to
    Idval (1000000).

    DECLARE TotalVal FLOAT
    SET TotalVal = 9.0 -- this could be any value

    UPDATE #temp1
    SET val = maxval
    WHERE idval IN
    (SELECT T1.idval
    FROM #temp1 AS T1
    JOIN #temp1 AS T2
    ON (T2.rank*1000000+T2.idval)<=(T1.rank*1000000+T1.id val)
    GROUP BY T1.rank, T1.idval
    HAVING SUM(T2.maxval) <= TotalVal)

    UPDATE #temp1
    SET val = TotalVal -
    (SELECT SUM(val)
    FROM #temp1)
    WHERE idval =
    (SELECT MIN(idval)
    FROM #temp1
    WHERE val=0 AND rank =
    (SELECT MIN(rank)
    FROM #temp1
    WHERE val=0))

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Transact SQL question

    Wow! Excellent approach!!

    .... too bad it is only good for the text books, because it performs much
    worse than the cursor solution, even after adding an index on Rank. The
    bigger the table, the worse the solution will perform in comparison to
    the cursor solution...

    With 1000 rows in the table (with 500 updated in the first query) the
    set based solution was about 11.2 times slower. This increased to 12.4
    with 5000 rows in the table (with 2500 updated in the first query).

    Below is a variation with the same approach. With 1000 rows, it
    performed worse than the cursor solution (2 times). With 5000 rows, it
    performed slightly better (a few percent).

    declare idval int
    declare rank int
    declare maxval float
    declare maxrank int
    declare TotalVal float
    declare sum float
    declare diff float
    set TotalVal = 19597.0

    set maxrank=(
    select MAX(T3.rank)
    from (
    select t1.rank,(
    select sum(maxval)
    from #temp1 t2
    where T2.rank<=T1.rank
    ) as rankmax
    from #temp1 t1
    ) AS T3
    where T3.rankmax <= TotalVal
    )

    UPDATE #temp1
    SET val = maxval
    WHERE rank < maxrank

    SET sum=(
    select sum(val)
    from #temp1
    where rank < maxrank
    )
    While sum < TotalVal
    Begin
    --select sum,getdate()
    SET diff = TotalVal - sum
    SET maxrank = (
    SELECT MIN(rank)
    FROM #temp1
    WHERE rank>=maxrank
    AND val=0
    )

    UPDATE #temp1
    SET val = CASE WHEN maxval < diff THEN maxval ELSE diff END
    WHERE idval =
    (SELECT MIN(idval)
    FROM #temp1
    WHERE val=0 AND rank = maxrank)

    SET sum=(
    select sum(val)
    from #temp1
    where rank <= maxrank
    )
    End


    Hope this helps,
    Gert-Jan


    David Portas wrote: 
    Gert-Jan Guest

  4. #4

    Default Re: Transact SQL question

    Thanks Gert-Jan. It would be useful to know some of the constraints of
    Peter's requirement: Number of rows, range of values in Maxval and
    TotalVal. My approach could be improved for some data sets by adding WHERE
    maxval<=TotalVal to the first query. Also it might perform better if it was
    possible to make Rank unique and indexed.

    Here's a thread on a similar problem with contributions by Steve Kass and I.
    http://tinyurl.com/gywh

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Transact SQL question

    Thanks David and Gert-Jan. I was mainly curious if my problem could be
    done without a cursor. Now I see it probably can be done - if not much
    more efficiently. The actual problem I'm working on is more complex
    (involving some subqueries to calculate the rank - which probably
    limits the effect of indexes) so if I have time I'll have to try both
    ways and test which is faster.

    Thanks again.

    "David Portas" <org> wrote in message news:<phx.gbl>... 
    Peter Guest

  6. #6

    Default Re: Transact SQL question

    Peter,

    SQL is not very good with this type of problems. This is because any set
    based solution will have to calculate massive amounts of data, and then
    discard most of it.

    For example, in your case the set based solution has to calculate the
    SUM(maxvalue) for each rank, even when you are only looking for one (the
    one that is closest to TotalValue).

    Gert-Jan

    P.S. If you put your cursor solution in a transaction, then it probably
    runs even faster because of the lower overhead.


    Peter wrote: [/ref]
    Gert-Jan Guest

  7. #7

    Default Re: Transact SQL question

    I could not test the following, but may work....

    UPDATE #temp1
    SET val = CASE WHEN D.maxval > TotalVal
    THEN D.newmaxval
    WHEN D.newmaxval > TotalVal
    THEN 0
    ELSE D.maxval
    END
    FROM ( SELECT idval, rank, maxval, val,
    ( SELECT MAX(t2.maxval)
    FROM #temp1 t2
    WHERE CAST(t2.rank AS VARBINARY) +
    CAST(t2.idval AS VARBINARY)<
    CAST(t1.rank AS VARBINARY) +
    CAST(t1.idval AS VARBINARY) )
    FROM #temp1 t1
    ) D ( idval, rank, maxval, val, newmaxval )
    INNER JOIN #temp1
    ON D.idval = #temp1.idval
    AND D.rank = #temp1.rank ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. #979 [Opn->Bgs]: [PEAR] Openmarket Transact support
    By pajoye@php.net in forum PHP Bugs
    Replies: 0
    Last Post: December 12th, 12:36 PM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. Transact-SQL Debugger
    By Neeraj in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 01:14 AM
  4. resource low - Transact-SQL
    By Otto Naesset in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 01:31 PM
  5. Transact-SQL syntax question
    By Rajan Murthy in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 03:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139