SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Alternative for Cursors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/03/2006 :  04:15:12  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 10/03/2006 :  04:20:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 10/03/2006 :  05:12:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 10/03/2006 :  07:14:34  Show Profile  Reply with Quote
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 - 10/03/2006 :  07:25:39  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 10/03/2006 :  07:38:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 10/03/2006 :  08:18:05  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 10/03/2006 :  08:37:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 10/04/2006 :  00:04:39  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 10/04/2006 :  00:46:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote



Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/04/2006 00:47:04
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000