Optomising Cursor Perfomance - 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 2000 Forums

Transact-SQL (2000)

Optomising Cursor Perfomance

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-02-22 : 05:03:15

Hi Fellow Code Warriors

I was just wondering if you guys can help me optomise the following cursor - It's running Error-Free but a bit slow

"a BIT slow" - hehehehehehe funny -
bit slow?

Enough jokes!

Cursor:


Declare @Enterprise varchar(10)	,
	@Activity	Varchar(50)	,
	@Var Varchar(15)	,
	@Tax varchar(15)	,
	@Vat Varchar(10)	,
	@sql NvarChar(400)

use NAD

Set NoCount On
/*
if 'Enterprise_History' in 	
	(
	Select name 
	from sysobjects
	)
	

	Begin
		Drop Table Enterprise_History
	end


Create Table Enterprise_History
(
	Enterprise_Nbr Varchar(10) Not Null	,
	Vat_Nbr Varchar(10)			,
	Tax_NBr varchar(10)			,
	Birt Varchar(50)			,
	Acti Varchar(50)			,
	Ceas Varchar(50)			,
	Deac Varchar(50)			,
	Unknown Varchar(50)
)
*/
Declare EntCur Cursor for

Select Distinct (Stat_Unit_Code)
from bf..History_Detail as D
Where not exists
	(
	Select Enterprise_Nbr
	from Enterprise_history as H
	where D.Stat_Unit_Code = H.Enterprise_Nbr
	)

Open EntCur

Fetch Next From EntCur Into
		@Enterprise

While @@fetch_status=0
	Begin
--		if 	(
--			Select  Count(Enterprise_Nbr)
--			from Enterprise_History
--			Where Enterprise_Nbr = @Enterprise
--			) = 0
			
--			Begin
				Insert into Enterprise_History 	(Enterprise_Nbr) 
							Values 	(@Enterprise)
--			End

	Fetch Next From EntCur Into
			@Enterprise

	End

Close EntCur
Deallocate EntCur


Declare Tab Cursor for

Select 	H.stat_unit_code as Enterprise_Nbr	, 
	RTR.Vat_Nbr	,
	RTR.Tax_Nbr	,
	RTrim(H.after_value_text) as [Action], 
	Left(H.maint_date, 11) + '  -  ' + Rtrim(E.Comment_Text) as Event	
from bf..History_Detail as H
Inner join bf..History_Event as E
On H.History_Event_NBr = E.History_Event_NBr
Inner join bf..Enterprise_External_link as El
On H.Stat_Unit_Code = El.Enterprise_Nbr
Inner join bf..Income_Tax as IT
On El.External_Nbr = IT.Income_Tax_Nbr
Inner Join bf..Representative_Tax_Rel as RTR
On IT.Income_Tax_Nbr = RTR.Tax_Nbr
Inner join Enterprise_history as EH
On H.stat_unit_code = EH.Enterprise_nBR
Where EH.ID_Nbr > '25348'

Open Tab

Fetch next From Tab Into
		@Enterprise,
		@Vat	,
		@Tax	,
		@Var	,
		@Activity

While @@fetch_status=0
	Begin
--		if 	(
--			Select  Count(Enterprise_Nbr)
--			from Enterprise_History
--			Where Enterprise_Nbr = @Enterprise
--			) > 0

--			Begin

		if 	@Var not in ('Acti', 'Birt', 'Deac', 'Ceas')
			Begin

				Update Enterprise_History 
					Set 	Vat_Nbr = '' + @Vat + '',
					Tax_Nbr = '' + @Tax + '',
					Unknown = '' + @Activity + ''
				where 	Enterprise_Nbr = '' + @Enterprise + ''


			End

		Else
			Begin
				Set @sql = 	'Update Enterprise_History 
							Set 	Vat_Nbr = ''' + @Vat + ''',
								Tax_Nbr = ''' + @Tax + ''',
								' + @Var + ' = ''' + @Activity + '''
						where 	Enterprise_Nbr = ''' + @Enterprise + ''''

				Exec sp_executesql @sql	
			End
--			End

		Fetch next From Tab Into
				@Enterprise,
				@Vat	,
				@Tax	,
				@Var	,
				@Activity
	End

Close Tab
Deallocate Tab

You can't teach an old mouse new clicks.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-22 : 05:34:55

Which bit is slow?
The creation of the cursors or the processing of rows - which I guess is the same as asking how many rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-02-22 : 05:52:26

The Processing of rows.
The First cursor is inserting about 8,5 million distinct rows from a table with about 19 million - It's pretty fast.

The second cursor (processing) is a bit too slow for my liking.
It's populating the rows created by the first cursor

You can't teach an old mouse new clicks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-22 : 07:34:32

Wait! You are using cursors on millions of records!!

That is probably 90% of your problem right there.

In the second cursor, you don't need both of the If statements, and I don't think you need the dynamic SQL...


Update Enterprise_History 
Set
	Vat_Nbr = @Vat,
	Tax_Nbr = @Tax,
	Unknown = case when @var not in ('Acti', 'Birt', 'Deac', 'Ceas') then @Activity else unknown end,
	Acti = case when @var = 'Acti' then @Activity else Acti end,
	Birt = case when @var = 'Birt' then @Activity else Birt end,
	Deac = case when @var = 'Deac' then @Activity else Deac end,
	Ceas = case when @var = 'Ceas' then @Activity else Ceas end
From Enterprise_History
where Enterprise_Nbr = @Enterprise

This still looks like it would be relatively simple to do this set-based, and that is definitely my recommendation.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-22 : 08:05:58

"Optomising Cursor Perfomance"
Now THAT's funny!!

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-22 : 08:14:48

The only way to optimize a cursor:

DON'T USE IT IN THE FIRST PLACE!

For example, this:

Declare EntCur Cursor for

Select Distinct (Stat_Unit_Code)
from bf..History_Detail as D
Where not exists
	(
	Select Enterprise_Nbr
	from Enterprise_history as H
	where D.Stat_Unit_Code = H.Enterprise_Nbr
	)

Open EntCur

Fetch Next From EntCur Into
		@Enterprise

While @@fetch_status=0
	Begin
--		if 	(
--			Select  Count(Enterprise_Nbr)
--			from Enterprise_History
--			Where Enterprise_Nbr = @Enterprise
--			) = 0
			
--			Begin
				Insert into Enterprise_History 	(Enterprise_Nbr) 
							Values 	(@Enterprise)
--			End

	Fetch Next From EntCur Into
			@Enterprise

	End

Close EntCur
Deallocate EntCur

Can be replaced with:

INSERT INTO Enterprise_History (Enterprise_Nbr)
SELECT DISTINCT Stat_Unit_Code
FROM bf..History_Detail AS D
WHERE NOT EXISTS(SELECT * FROM Enterprise_history AS H 
WHERE D.Stat_Unit_Code = H.Enterprise_Nbr)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-02-22 : 08:49:40

Thanks a 8,5 Million
heheheehehehe

Yeah - it looks faster!

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources