More cursor questions... 3

[icemel]

Below is an excerpt from an article from SQL-Server-Performance.com. They sound like good ideas. However, they give no examples of how to transform a cursor using these methods. I suppose I should use my imagination.

Is there someone who can illustrate an example of how to use each method below. I guess I am still stuck in the cursor mentality, in that I don't believe that you can always avoid cursors. But then, I am no SQL genius.

Here's an example. Let's say you have a table and you want to update a datetime field, but the value by which to update will be different in each case. In some cases, you want to add 7 days to the datetime field. In other cases, 1, 2, 3 days... and it's dependent on other values in the record. How could you use the techniques below to do this?

I do appreciate everyone's advice NOT to use cursors. But how does one get around them, for example, in the case above?

Thanks

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

Use WHILE LOOPS

Use temp tables

Use derived tables

Use correlated sub-queries

Use the CASE statement

Perform multiple queries

 

[gmmastros]

Something like this...

Declare @Temp Table(Id Integer, SomeDate DateTime, DaysToAdd Integer)

Insert Into @Temp Values(1, '2006-06-01', 1)
Insert Into @Temp Values(2, '2006-06-01', 2)
Insert Into @Temp Values(3, '2006-06-01', 3)
Insert Into @Temp Values(4, '2006-06-01', 4)
Insert Into @Temp Values(5, '2006-06-01', 5)
Insert Into @Temp Values(6, '2006-06-01', 6)
Insert Into @Temp Values(7, '2006-06-01', 7)
Insert Into @Temp Values(8, '2006-06-01', 8)

Select * From @Temp

Update @Temp 
Set    SomeDate = SomeDate + DaysToAdd

Select * From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

temp and while example

use Pubs
go
select identity(int,1,1) as Id,au_id into #temp
from authors



declare @MaxID int,@loopid int,@au_id varchar(666)
select @loopid = 1,@MaxID = max(id) from #temp
while @loopid < @MaxID
begin

select @au_id =au_id from #temp
where id =@loopid
--do your stuff here
--example, print au_id
print 'au_id  ==  ' + @au_id

set @loopid = @loopid + 1

end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

So, from what I gather, it is faster to use a temp table or table variable than use a cursor?

I guess I would have to test it.

Thanks

 

[gmmastros]

There are always exceptions, but generally speaking the best performing to the worst performing would be...

Use derived tables
Use correlated sub-queries
Use table variables
Use temp tables
Use WHILE LOOPS
Use cursor

Be careful of the exceptions! Some times a cursor will give you the best performance, but usually it won't. It also depends on the amount of data you are dealing with. Certainly, a procedure that 'plays' with a dozen records will be fast no matter how you code it. When you have a couple hundred thousand records (or more), and you want to improve performance, the best suggestion would be to try it multiple ways to see what works best for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Often people use cursors to insert or update 1 record ata time instead of the set-based solution because they don't know the syntax to insert or update multiple records particularly when joining to other tables in the insert.

So if your cursor query is something like:

select t1.field2, t2.field3, t2.field 4 from table1 t1 join table2 t2
on t1.field1 = t2.field2 where t2.field3 = 'test'

then you would insert all these recrods without a cursor with the following statment

Insert into table3 (field2, field3, field4)
select t1.field2, t2.field3, t2.field 4 from table1 t1 join table2 t2
on t1.field1 = t2.field2 where t2.field3 = 'test'

to do an update

update t3 
set t3.field2 = t1.field2,
t3.field3 = t2.field3,
t3.field4 = t2.field4
from table3 t3 join
table1 t1 on t3.idfield = t1.idfield
join table2 t2
on t1.field1 = t2.field2 where t2.field3 = 'test'

other things to try are to use a case statement inthe insert or update to take care of conditional processing that was done using a cursor. Something like:

update t3 
set t3.field2 = t1.field2,
t3.field3 = case when t1.field3 >10 then t1.field3 else t1.field4 - 27 end
from table3 t3 join
table1 t1 on t3.idfield = t1.idfield

Questions about posting. See faq183-874
Click here to help with Hurricane Relief