Nasty performance issue. - 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)

Nasty performance issue.

Author

Topic

akki007
Starting Member

2 Posts

Posted - 2007-08-02 : 07:00:11

I have a cursor that is quite straight forward but it is processing one row per second and has a total of 847,000 rows to process. If anyone feels the urge to do so, could they make any suggestions for me? thanks! cursor...

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPEND
as

begin

DECLARE @I_UPN varchar(13)
DECLARE @I_SURNAME varchar(50)
DECLARE @I_FORENAME varchar(50)
DECLARE @I_DOB datetime
DECLARE @I_GENDER varchar(1)
DECLARE @I_LEA varchar(3)
DECLARE @I_DFES varchar(4)
DECLARE @I_ATTEND_YEAR varchar(4)
DECLARE @I_WEEK_BEGINNING datetime
DECLARE @I_ATTEND_CODES varchar(14)

declare @cnt int

set nocount on

declare cur CURSOR

for select upn,
surname,
forename,
dob,
gender,
'353' as lea,
dfes,
attend_year,
week_beginning,
attend_codes
from tmpATTEND_IMPORT

for read only

open cur

fetch from cur into
@I_UPN,
@I_SURNAME,
@I_FORENAME,
@I_DOB,
@I_GENDER,
@I_LEA,
@I_DFES,
@I_ATTEND_YEAR,
@I_WEEK_BEGINNING,
@I_ATTEND_CODES

truncate table tmpATTEND_IMPORT_APPENDED

while @@fetch_status = 0
begin
set @cnt = (select count(*) as cnt from tmpATTEND_IMPORT_APPENDED
where upn = @I_UPN
and surname = @I_SURNAME
and forename = @I_FORENAME
and dob = @I_DOB
and gender = @I_GENDER
and lea = @I_LEA
and dfes = @I_DFES
and attend_year = @I_ATTEND_YEAR)

if @cnt = 0 insert tmpATTEND_IMPORT_APPENDED(
upn,
surname,
forename,
dob,
gender,
lea,
dfes,
attend_year,
week_beginning,
attend_codes)
values (
@I_UPN,
@I_SURNAME,
@I_FORENAME,
@I_DOB,
@I_GENDER,
@I_LEA,
@I_DFES,
@I_ATTEND_YEAR,
@I_WEEK_BEGINNING,
@I_ATTEND_CODES)

else update tmpATTEND_IMPORT_APPENDED
set attend_codes = attend_codes+@I_ATTEND_CODES
where upn = @I_UPN and
surname = @I_SURNAME and
forename = @I_FORENAME and
dob = @I_DOB and
gender = @I_GENDER and
lea = @I_LEA and
dfes = @I_DFES and
attend_year = @I_ATTEND_YEAR

fetch next from cur into
@I_UPN,
@I_SURNAME,
@I_FORENAME,
@I_DOB,
@I_GENDER,
@I_LEA,
@I_DFES,
@I_ATTEND_YEAR,
@I_WEEK_BEGINNING,
@I_ATTEND_CODES

end

close cur

deallocate cur

end
GO

pootle_flump

1064 Posts

Posted - 2007-08-02 : 07:47:51

quote:
Originally posted by akki007

I have a cursor ...

That's where you went wrong. BoL really should warn about using cursors or make you undertake a quiz before you can open one.

You would do this much more efficiently in a set based manner.
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-02 : 14:00:11

I think you can get rid of the cursor and do it in one query. Something like:

insert 
	tmpATTEND_IMPORT_APPENDED
	(
		upn,
		surname,
		forename, 
		dob,
		gender,
		lea, 
		dfes, 
		attend_year, 
		week_beginning, 
		attend_codes
	)
select 
	upn, 
	surname, 
	forename, 
	dob, 
	gender, 
	'353' as lea, 
	dfes, 
	attend_year, 
	week_beginning, 
	SUM(*)
from 
	tmpATTEND_IMPORT
group by
	upn, 
	surname, 
	forename, 
	dob, 
	gender, 
	'353', 
	dfes, 
	attend_year, 
	week_beginning

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources