Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Alternative for Cursors

Author  Topic 

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-03 : 04:15:12
Gurus
I am extensively using cursors in my Sps and in queries which is actually slowing up the operations.Please suggest me something.

Regards
Nitin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 04:20:12
WHILE and some variables can do the trick.
It is hard to tell, without knowing what to CURSOR is really doing today.
Post code here.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

TimothyJames
Starting Member

2 Posts

Posted - 2006-10-03 : 05:12:56
There are other ways to use like cursor depends on your specification. If your trying to fetch the data and update it at the same time, you can use UPDATE MASS instead or you can create temp table and insert those valid records on it. Using cursor really make your query very slow and your server as well.

________
HTH
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-03 : 07:14:34
Or, you can use SET BASED querys

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-03 : 07:25:39
Hi
Can you please elaborate what are these set based queries?and wnt the performance be effected if we used temp tables?
Regards
Nitin
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-03 : 07:38:13
Set-based operations do not need to use temp tables, but even if they did they will be faster than cursors.

It's fairly pointless to go into details without an example of code that you're trying to fix. Post something we can look at and try to change to set-based operations. Something small (<100 lines) would be better as a start.
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-03 : 08:18:05
hi
This is the code.Please suggest

CREATE PROC dbo.SearchDisplayGrid30_P
(
@v_SQLSelect Varchar(8000)
, @v_SQLRest text
, @v_TimeStamp Varchar(100)
, @v_LonFields Varchar(4000) = NULL
)
AS
DECLARE @v_TempDbName Varchar(200), @v_ProId INT

DECLARE @s_dateID INT
DECLARE @s_date DATETIME
DECLARE @e_date DATETIME

set @s_date = getdate()

INSERT INTO TBL_SEARCHLOG3(StartDateTime) values(@s_date)
set @s_dateID = @@identity



SELECT @v_TempDbName = 'tempdb..##' + @v_TimeStamp
IF OBJECT_ID(@v_TempDbName) IS NOT NULL
EXEC( 'DROP TABLE ##' + @v_TimeStamp)

EXEC( @v_SQLSelect + ' INTO ##' + @v_TimeStamp + ' ' + @v_SQLRest)

EXEC( 'DECLARE Search_Cur CURSOR FOR SELECT DISTINCT PRO_ID FROM ##' + @v_TimeStamp)
OPEN Search_Cur
FETCH NEXT FROM Search_Cur INTO @v_ProId
WHILE( @@FETCH_STATUS = 0)
BEGIN
EXEC( 'DELETE ##' + @v_TimeStamp + ' WHERE PRO_ID = ' + @v_ProId + '
AND LoanPosition NOT IN ( SELECT min(LoanPosition) FROM ##' + @v_TimeStamp + ' WHERE PRO_ID = ' + @v_ProId + ')')

FETCH NEXT FROM Search_Cur INTO @v_ProId
END
CLOSE Search_Cur
DEALLOCATE Search_Cur

IF LTRIM( RTRIM( @v_LonFields)) <> ''
EXEC( 'SELECT * FROM ##' + @v_TimeStamp + ' ORDER BY ' + @v_LonFields)
ELSE
EXEC( 'SELECT * FROM ##' + @v_TimeStamp)
EXEC( 'DROP TABLE ##' + @v_TimeStamp)


set @e_date = getdate()

UPDATE TBL_SEARCHLOG3 set EndDateTime = @e_date, TimeTaken = datediff(ms, @s_date, @e_date) where Log_ID= @s_dateID
GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 08:37:28
We can't use that since one of the parameters is the dynamic SQL statement.
How about you try to explain to us of the purpose of this code. The business logic.



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-04 : 00:04:39
Yes i understand,but may be you can give me a example and let me know.
Regards
Nitin
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 00:46:46



Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -