Cursor efficiency? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Cursor efficiency?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-08 : 13:24:18

I am using nested cursors in my script below, and wonder if there is a more efficient way please?


USE ar
GO
DECLARE @mortgage INT,
		@mortgage_sequence int,
		@getMortgage CURSOR,
		@notes_1 varchar(MAX),
		@notes_2 varchar(MAX),
		@notes_3 varchar(MAX),
		@notes_4 varchar(MAX),
		@notes_5 varchar(MAX),
		@notes_6 varchar(MAX),
		@notes_7 varchar(MAX),
		@notes_8 varchar(MAX),
		@notes_9 varchar(MAX),
		@notes_10 varchar(MAX),
		@notes_11 varchar(MAX),
		@notes_12 varchar(MAX),
		@notesComplete varchar(MAX),
		@addedUser varchar(255),
		@addedDate varchar(255),
		@amendedUser varchar(255),
		@amendedDate varchar(255),
		@sequence int,
		@getDetail CURSOR
		

SET @getMortgage = 	CURSOR FOR
					SELECT DISTINCT Mortgage_Number, Mortgage_Note_Sequence_No
					FROM format_additional_notes
					GROUP BY Mortgage_Number, Mortgage_Note_Sequence_No
					ORDER BY Mortgage_Number ASC
OPEN @getMortgage
	FETCH NEXT
	FROM @getMortgage INTO @mortgage, @mortgage_sequence
WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SET @getDetail = CURSOR FOR
			SELECT 	ltrim(rtrim(Additional_Text_1)),
					ltrim(rtrim(Additional_Text_2)),
					ltrim(rtrim(Additional_Text_3)),
					ltrim(rtrim(Additional_Text_4)),
					ltrim(rtrim(Additional_Text_5)),
					ltrim(rtrim(Additional_Text_6)),
					ltrim(rtrim(Additional_Text_7)),
					ltrim(rtrim(Additional_Text_8)),
					ltrim(rtrim(Additional_Text_9)),
					ltrim(rtrim(Additional_Text_10)),
					ltrim(rtrim(Additional_Text_11)),
					ltrim(rtrim(Additional_Text_12)),
					Mortgage_Note_Sequence_No,
					Extra_Added_by_User,
					Extra_Added_on_Date,
					Extra_Amended_By_User,
					Extra_Amended_By_Date
					
			FROM  format_additional_notes
			WHERE Mortgage_Number = @mortgage AND Mortgage_Note_Sequence_No = @mortgage_sequence
			ORDER BY Mortgage_Note_Sequence_No
		OPEN @getDetail
		SET @notesComplete = ''
		FETCH NEXT FROM @getDetail INTO @notes_1,
										@notes_2,
										@notes_3,
										@notes_4,
										@notes_5,
										@notes_6,
										@notes_7,
										@notes_8,
										@notes_9,
										@notes_10,
										@notes_11,
										@notes_12,
										@sequence,
										@addedUser,
										@addedDate,
										@amendedUser,
										@AmendedDate
		WHILE (@@FETCH_STATUS = 0)
		  BEGIN
		   SET @notesComplete = @notesComplete + 
								ISNULL(@notes_1,'') + ' ' + 
								ISNULL(@notes_2,'') + ' ' + 
								ISNULL(@notes_3,'') + ' ' + 
								ISNULL(@notes_4,'') + ' ' + 
								ISNULL(@notes_5,'') + ' ' + 
								ISNULL(@notes_6,'') + ' ' + 
								ISNULL(@notes_7,'') + ' ' + 
								ISNULL(@notes_8,'') + ' ' + 
								ISNULL(@notes_9,'') + ' ' + 
								ISNULL(@notes_10,'') + ' ' + 
								ISNULL(@notes_11,'') + ' ' + 
								ISNULL(@notes_12,'')
								
		   FETCH NEXT FROM @getDetail INTO 	@notes_1,
											@notes_2,
											@notes_3,
											@notes_4,
											@notes_5,
											@notes_6,
											@notes_7,
											@notes_8,
											@notes_9,
											@notes_10,
											@notes_11,
											@notes_12,
											@sequence,
											@addedUser,
											@addedDate,
											@amendedUser,
											@AmendedDate
		  END
		
		
		INSERT INTO format_additional_notes_1 
		   (Mortgage_Number,
			Mortgage_Note_Sequence_No,
			Additional_Text,
			Extra_Added_By_User,
			Extra_Added_on_Date,
			Extra_Amended_By_User,
			Extra_Amended_By_Date)
		VALUES 
			( @mortgage,
			  @sequence,
			  @notesComplete,
			  @addedUser,
			  @addedDate,
			  @amendedUser,
			  @amendedDate)
				
		CLOSE @getDetail
		DEALLOCATE @getDetail
		FETCH NEXT
		FROM @getMortgage INTO @mortgage, @mortgage_sequence
	END
CLOSE @getMortgage
DEALLOCATE @getMortgage
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 13:28:40

Can you post your full requirement please with table structures & sample data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 13:36:47

Is that even valid MICROSOFT T-SQL syntax?

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-08 : 13:55:08

quote:
Originally posted by Peso

Is that even valid MICROSOFT T-SQL syntax?

E 12°55'05.25"
N 56°04'39.16"

As a SQL newbie I don't really know the difference.

But my script takes 6:56 with sample data of 1000 rows. My actual data is 2.5 million rows. So I need to find a way of speeding things up.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:17:07

This maybe?

SELECT DISTINCT	fan1.Mortgage_Number,
		fan1.Mortgage_Note_Sequence_No,
		LTRIM(RTRIM((
				SELECT		TOP 100 PERCENT
						' ' + ISNULL(s2.Additional_Text_1,'')
						+ ' ' + ISNULL(s2.Additional_Text_2, '')
						+ ' ' + ISNULL(s2.Additional_Text_3, '')
						+ ' ' + ISNULL(s2.Additional_Text_4, '') 
						+ ' ' + ISNULL(s2.Additional_Text_5, '') 
						+ ' ' + ISNULL(s2.Additional_Text_6, '') 
						+ ' ' + ISNULL(s2.Additional_Text_7, '') 
						+ ' ' + ISNULL(s2.Additional_Text_8, '') 
						+ ' ' + ISNULL(s2.Additional_Text_9, '') 
						+ ' ' + ISNULL(s2.Additional_Text_10, '') 
						+ ' ' + ISNULL(s2.Additional_Text_11, '') 
						+ ' ' + ISNULL(s2.Additional_Text_12, '')
				FROM		Format_Additional_Notes AS fan2
				WHERE		fan2.Mortgage_Number = fan1.Mortgage_Number
				ORDER BY	fan2.Mortgage_Note_Sequence_No
				FOR XML		PATH('')
			))) AS Additional_Text,
		fan1.Extra_Added_By_User,
		fan1.Extra_Added_on_Date,
		fan1.Extra_Amended_By_User,
		fan1.Extra_Amended_By_Date
FROM		Format_Additional_Notes AS fan1

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-08 : 14:25:31

Thanks for your reply. But I don't understand your 's2.' reference.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:40:23

The curse of copy&paste...

SELECT DISTINCT	fan1.Mortgage_Number,
		fan1.Mortgage_Note_Sequence_No,
		LTRIM(RTRIM((
				SELECT		TOP 100 PERCENT
						' ' + ISNULL(fan2.Additional_Text_1,'')
						+ ' ' + ISNULL(fan2.Additional_Text_2, '')
						+ ' ' + ISNULL(fan2.Additional_Text_3, '')
						+ ' ' + ISNULL(fan2.Additional_Text_4, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_5, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_6, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_7, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_8, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_9, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_10, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_11, '') 
						+ ' ' + ISNULL(fan2.Additional_Text_12, '')
				FROM		Format_Additional_Notes AS fan2
				WHERE		fan2.Mortgage_Number = fan1.Mortgage_Number
				ORDER BY	fan2.Mortgage_Note_Sequence_No
				FOR XML		PATH('')
			))) AS Additional_Text,
		fan1.Extra_Added_By_User,
		fan1.Extra_Added_on_Date,
		fan1.Extra_Amended_By_User,
		fan1.Extra_Amended_By_Date
FROM		Format_Additional_Notes AS fan

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources