July 1, 2011 at 1:24 am
something like this?
DECLARE @pk [type], @var [type]
DECLARE CRSR_TMP
CURSOR FOR
SELECT PK FROM
OPEN CRSR_TMP
FETCH NEXT
FROM CRSR_TMP
INTO @pk
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @var = (SELECT col1 from
where PK = @pk)
/* some logic...*/
FETCH NEXT
FROM CRSR_TMP
INTO @pk
END
CLOSE CRSR_TMP
DEALLOCATE CRSR_TMP
Edit: This is not a fast solution, depends on number of records...
July 1, 2011 at 7:44 am
A cursor is nothing more than a rersource intensive loop. Don't use a cursor.
Please post what the other logic is for each PK and we'll try to show you how to do it with high performance, set-based code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2011 at 9:05 pm
If you do find yourself stuck in a situation where you need to cycle through every record in a table, here is a good example of how to code a loop without using a cursor:
Jason
Webmaster at SQL Optimizations School
July 6, 2011 at 1:13 am
jasonwi1202 (7/5/2011)
If you do find yourself stuck in a situation where you need to cycle through every record in a table, here is a good example of how to code a loop without using a cursor:
I wouldn't consider the link as a good example. It starts promising ("Never, ever use Cursors."). But the alternative presented sometimes is as bad as the c.u.r.s.o.r. *cough* itself (a WHILE loop in one of the least performing ways :sick:).
July 6, 2011 at 3:27 am
July 6, 2011 at 5:39 am
kritikamehtani (7/6/2011)
logic :---SELECT @message = queue_message FROM tablename
WHERE PK= @pk
IF (@PK is not null and @var2 = 1 )
BEGIN
EXEC dbo.func()
@command = @message,
@isDebug = 0
END
SELECT * FROM table
WHERE PK = @pk
the above lines of code must get executed for each record.
Please post the code for dbo.func().
I don't think it MUST be coded this way...
July 11, 2011 at 6:30 am
u can use while loop instead of cursors, as cursors are always costlier.
use a temporary table get all the values to temporary table process one by one using variable.
i am posting some piece of code. try it if its solve ur problem.
CREATE TABLE #TempSolutions
(
ProjectId BIGINT,
SolutionTypes NVARCHAR(2000)
)
SET @Count = ( SELECT COUNT(id)
FROM PalladiumConnection.dbo.WI_index
)
INSERT INTO #TempSolutions
(
ProjectId,
SolutionTypes
)
SELECT id,
solution_types
FROM PalladiumConnection.dbo.WI_index
WHILE @Count >= 1
BEGIN
SET @ProjectId = ( SELECT TOP 1
projectid
FROM #TempSolutions
)
SET @SolutionTypes = ( SELECT TOP 1
solutionTypes
FROM #TempSolutions
)
SELECT @ProjectId,
string
FROM PalladiumShare.dbo.split(@SolutionTypes, ',')
SET @Count = @Count - 1
DELETE FROM #TempSolutions
WHERE ProjectId = @ProjectId
END
July 11, 2011 at 6:58 am
ghanshyam.kundu (7/11/2011)
u can use while loop instead of cursors, as cursors are always costlier.use a temporary table get all the values to temporary table process one by one using variable.
i am posting some piece of code. try it if its solve ur problem
....
How much faster do you think your solution will be? It's processing one row at the time just like the c.u.r.s.o.r.
Also, be careful with statements like "as cursors are always costlier". I'm confident there are cases where that just isn't true.
It's the same like arguing if a horse will be the better choice than a mule in terms of average speed over a long distance when there's a motorbike available. 😉
July 11, 2011 at 7:09 am
WHILE loops are not necessarily faster than cursors and in fact may be slower than some cursors.
The problem with both WHILE loops and cursors is that they retrieve each input row separately and then do individual insert/update/delete operations on a row-by-agonizing-row basis, taking a performance hit from the accompanying overhead.
When we talk about set-based processing, this is what we mean:
In SQL it is faster to process (select, insert, update, delete) a thousand rows with one statement than it is to process one statement a thousand times. WHILE loops and cursors both follow the slower second path, and with rare exceptions they are used unnecessarily.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 11, 2011 at 10:43 am
Hi,
We had some comments on the page I posted above in relation to cursor speed and whatnot. In the end, I went to the Microsoft SQL updates/service packs and tried to figure out how they have changed over time. It looks like cursors were not so good prior to SQL 2005 SP2. Since then, they have been fixed through a bunch of updates and should be performing much better than they used too. So the stigma of cursors started prior to 2005 SP2 and has been carrying forward since then... from what I read on the updates, they required a lot of tuning by Microsoft, so it is understandable that people have some hatred towards them. 🙂
Jason
Webmaster at SQL Optimizations School
July 11, 2011 at 1:00 pm
Jason,
Although we often use hate language when we joke about cursors, blind hatred is not our motivation. The simple fact is they are far too often used as a procedural "crutch", which results in slow-running solutions. The huge performance differences can still be proven even under SQL 2008, as the following code should demonstrate. On my laptop, the set-based solution populates the target table almost 100 times faster than the cursor based solution, and a WHILE loop would run even slower.
If anyone would post up some examples where a cursor solution outperforms a set-based solution, I will look at them respectfully.
Bob
Master of Nothing In Particular
set nocount on;
if object_ID(N'tempdb..#source') is not null drop table #source
if object_ID(N'tempdb..#target') is not null drop table #target
select object_id,name,column_id
into #source
from sys.all_columns
select *
into #target
from #source
where 1 = 0
print '--------------------------------------------------'
print 'set-based'
declare @timer datetime = getdate()
insert into #target
select * from #source
select DATEDIFF(ms,@timer,getdate()) as [Set-based Time In Milliseconds]
print '--------------------------------------------------'
go
select COUNT(*) as [Set-based Rows Inserted] from #target
truncate table #target
print '--------------------------------------------------'
print 'cursor'
DECLARE @objectID int, @name varchar(100), @columnID int;
DECLARE belovedCursor CURSOR FAST_FORWARD FOR
SELECT object_id,name,column_id
FROM #source
OPEN belovedCursor;
FETCH NEXT FROM belovedCursor
INTO @ObjectID,@name,@columnID
declare @timer datetime = getdate()
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #target
SELECT @objectID,@name,@columnID
FETCH NEXT FROM belovedCursor
INTO @ObjectID,@name,@columnID
END
select DATEDIFF(ms,@timer,getdate()) as [Set-based Time In Milliseconds]
CLOSE belovedCursor;
DEALLOCATE belovedCursor;
select COUNT(*) as [Cursor Rows Inserted] from #target
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2011 at 5:39 am
all,,
i got my solution... thanks newaz...
July 16, 2011 at 10:41 am
The Dixie Flatline (7/11/2011)
Jason,Although we often use hate language when we joke about cursors, blind hatred is not our motivation. The simple fact is they are far too often used as a procedural "crutch", which results in slow-running solutions. The huge performance differences can still be proven even under SQL 2008, as the following code should demonstrate. On my laptop, the set-based solution populates the target table almost 100 times faster than the cursor based solution, and a WHILE loop would run even slower.
If anyone would post up some examples where a cursor solution outperforms a set-based solution, I will look at them respectfully.
Bob
Master of Nothing In Particular
set nocount on;
if object_ID(N'tempdb..#source') is not null drop table #source
if object_ID(N'tempdb..#target') is not null drop table #target
select object_id,name,column_id
into #source
from sys.all_columns
select *
into #target
from #source
where 1 = 0
print '--------------------------------------------------'
print 'set-based'
declare @timer datetime = getdate()
insert into #target
select * from #source
select DATEDIFF(ms,@timer,getdate()) as [Set-based Time In Milliseconds]
print '--------------------------------------------------'
go
select COUNT(*) as [Set-based Rows Inserted] from #target
truncate table #target
print '--------------------------------------------------'
print 'cursor'
DECLARE @objectID int, @name varchar(100), @columnID int;
DECLARE belovedCursor CURSOR FAST_FORWARD FOR
SELECT object_id,name,column_id
FROM #source
OPEN belovedCursor;
FETCH NEXT FROM belovedCursor
INTO @ObjectID,@name,@columnID
declare @timer datetime = getdate()
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #target
SELECT @objectID,@name,@columnID
FETCH NEXT FROM belovedCursor
INTO @ObjectID,@name,@columnID
END
select DATEDIFF(ms,@timer,getdate()) as [Set-based Time In Milliseconds]
CLOSE belovedCursor;
DEALLOCATE belovedCursor;
select COUNT(*) as [Cursor Rows Inserted] from #target
Bob, interesting code to have handy, thanks for posting.
Coupla items. I think you have a typo. near the bottom where you alias the elapsed time for the cursor runtime duration: you alias the elapsed time as "Set" based. Just pointing this out in case the o.p. runs it and is confused.
More interesting to me is that the execution times on my old Gateway Vista 1.6 GHz laptop were actually pretty close to each other, which surprised me given your results. I had execution times of 250 and 340 ms for set- and cursor-based code, respectively. Of course, that still means the cursor is 40% slower on a machine that's doing nothing else, and fully confirms that the cursor is a poor performer. Just nowhere near the 100x slower times you saw.
SQL Server 2005 Express, 32 bit.
Rich
July 16, 2011 at 11:08 am
kritika (7/14/2011)
all,,i got my solution... thanks newaz...
Very cool. Would you mind posting it so that others may learn from your experience, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply