Author |
Topic |
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-10-03 : 04:15:12
|
GurusI am extensively using cursors in my Sps and in queries which is actually slowing up the operations.Please suggest me something.RegardsNitin |
|
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 LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-10-03 : 07:25:39
|
HiCan you please elaborate what are these set based queries?and wnt the performance be effected if we used temp tables?RegardsNitin |
|
|
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. |
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-10-03 : 08:18:05
|
hiThis is the code.Please suggestCREATE 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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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.RegardsNitin |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-04 : 00:46:46
|
Peter LarssonHelsingborg, Sweden |
|
|
|