Avoid cursor for multiple columns and multiple variables

  • Hi Everyone,

    I wanted to replace cursor with some select statement for multiple columns and multiple variables for speed issue.

    Is it possible ?

    For example:

    DECLARE C2 cursor FOR

    select ColumnA,ColumnB,ColumnC,ColumnD from tblTableName where ID = 1 order by ColumnA

    OPEN C2

    FETCH NEXT FROM c2 INTO

    @temp_A,@temp_B,@temp_C,@temp_D

    Set @temp_var = 1

    WHILE @@FETCH_STATUS = 0

    BEGIN

    if @temp_var = 1

    begin

    Set @U1A = @temp_A

    Set @U1B = @temp_B

    Set @U1C = @temp_C

    end

    if @temp_var = 2

    begin

    Set @U2A = @temp_A

    Set @U2B = @temp_B

    Set @U2C = @temp_C

    end

    if @temp_var = 3

    begin

    Set @U3A = @temp_A

    Set @U3B = @temp_B

    Set @U3C = @temp_C

    end

    if @temp_var = 4

    begin

    Set @U4A = @temp_A

    Set @U4B = @temp_B

    Set @U4C = @temp_C

    end

    Set @temp_var = @temp_var + 1

    FETCH NEXT FROM C2 INTO @temp_A,@temp_B,@temp_C,@temp_D

    end

    CLOSE C2

    DEALLOCATE C2

    Can it be implemented without cursor?

  • Yes. First use ROW_NUMBER in a common table expression to get a row number for each of the top four rows in your table ordered by ColumnA. Then you can do something like this for each set of variables:

    SELECT

    @U1A = ColumnA

    @U1B = ColumnB

    @U1C = ColumnC

    FROM MyCTE

    WHERE RowNumber = 1

    Have a go at that, and post back if there's anything you don't understand. If you tell us your exact requirement, we may be able to suggest an even better way of doing what you're trying to achieve.

    John

  • I want to store each rows' column values in different variables. @U1A, @U1B,..@U2A are those variables.. I m using that in other part of my stored procedure..

    Say if i have a table like

    A B C D

    1 2 3 4

    9 8 7 6

    3 4 5 6

    6 7 8 9

    I wanted

    @U1A = 1,

    @U2A = 9,

    @U3A = 3,

    @U4A = 6

    @U1A =2,

    @U2B =8

    @U3B =4

    and so on.. in sql server with single select statement if possible..

  • IMO , you are asking the wrong question.

    What are you doing with this data once it is in variables ?

    If you tell us the whole problem then you may well find that variables are not needed anyway.



    Clear Sky SQL
    My Blog[/url]

  • I have one cursor returning id and other master information.

    For each id we are collecting multiple rows information from table.

    Here we have to check for first 4 rows of that id. Store it in (4*4 = 16)variables and implement our business logic.

    My store procedure is working fine but i want it to be more faster.

  • IMO , cursoring over 16 rows should really be causing you a lot of pain in itself.

    -- EDIT Typo , "should" should be "shouldn't"

    Still , is this what you are after ?

    with cteX

    as(

    select ColumnA,ColumnB,ColumnC,ColumnD,row_number() over(order by ColumnA) as Rown

    from TableName

    where ID = 1

    )

    Select @v1 = min(case when RowN = 1 then ColumnA else null end),

    @v2 = min(case when RowN = 1 then ColumnB else null end),

    @v3 = min(case when RowN = 1 then ColumnC else null end),

    @v4 = min(case when RowN = 1 then ColumnD else null end),

    @v5 = min(case when RowN = 2 then ColumnA else null end),

    @v6 = min(case when RowN = 2 then ColumnB else null end),

    @v7 = min(case when RowN = 2 then ColumnC else null end),

    @v8 = min(case when RowN = 2 then ColumnD else null end),

    @v9 = min(case when RowN = 3 then ColumnA else null end),

    @v10 = min(case when RowN = 3 then ColumnB else null end),

    @v11= min(case when RowN = 3 then ColumnC else null end),

    @v12 = min(case when RowN = 3 then ColumnD else null end),

    @v13 = min(case when RowN = 4 then ColumnA else null end),

    @v14 = min(case when RowN = 4 then ColumnB else null end),

    @v15 = min(case when RowN = 4 then ColumnC else null end),

    @v16 = min(case when RowN = 4 then ColumnD else null end)

    from cteX



    Clear Sky SQL
    My Blog[/url]

  • Thanks Dave, I was trying somthing like this.

    Because of my business logic i have to handle 16 variables.This will work though i found just a very small time difference from cursor one.

  • Thats pretty much as i had thought,

    are you able to post the rest of the code ?

    Remember though , we are unpaid volunteers here and wont be doing hours of work on your behalf.



    Clear Sky SQL
    My Blog[/url]

  • Please check the attached.

  • Did you measure the time to declare the cursor and to determine the value of @Proposal vs. actually processing the rows?

    I'm not sure if those two together with the rest of the cursor stuff will actually require more time than the nested cursor you just replaced. Maybe it's time to run a profiler trace against a limited number of cursor loops to see where the actual bottleneck is.

    As a side note: you might also consider rewriting the code to populate @DestinatonCountry e.g. by using the FOR XML PATH approach.

    I think it should be possible to replace all those "loopings" with set based solutions (maybe requiring a temp table or two...).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply