January 21, 2011 at 4:29 am
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?
January 21, 2011 at 4:50 am
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
January 21, 2011 at 5:36 am
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..
January 21, 2011 at 5:41 am
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.
January 21, 2011 at 5:56 am
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.
January 21, 2011 at 6:06 am
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
January 21, 2011 at 6:42 am
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.
January 21, 2011 at 7:03 am
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.
January 21, 2011 at 7:18 am
Please check the attached.
January 21, 2011 at 8:20 am
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...).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply