Agent Job Duration exceeds manually launched program duration

[BerndSX]

Hi folks,
I am really despaired.
I have a SQL Programm (sql server 2005), nothing wild, which consists mostly on a static cursor which runs over about 100000 Records and executes a stored procedure copying these records to other tables.

When run manually in the Management Studio , the job is done in about 20 to 30 minutes. If it runs inside a sql server agent job it goes banana with over 20 hours.

What possible reasons are there for such an behaviour?

see enclosed the code

set nocount on 

	declare cc1 cursor local static for
		select 
			P.projid, Survey, einart
		from 
			i_data.Projekt P

	open cc1 
	fetch next from cc1 into @projid, @survey, @einart
	while @@fetch_status=0
	begin
		declare cc3 cursor local static  for
			select 
				C.InterviewNumber, C.ContactNumber, C.ContactDate, C.ResponseStatus,
				C.InterviewerNumber, C.AppointmentTime, (C.TotalTime - C.IntroductionTime) as Netto, ContactID
			from 
				(
				(
				select 
					SurveyName, InterviewNumber, C.SMDTContactDate, responsestatus
				from 
					dbo.tmpCatiContactLog_Archiv C 
				where 
					surveyname=@survey and toiSMS=0
				)
				except
				Select 
					K_STUDID, LFD, K_Dat, K_RC
				from 
					i_Flow.Kontakt 
				where 
					projid=@projid and K_STUDID=@survey
				)X INNER JOIN dbo.tmpCatiContactLog_Archiv C 
					on C.SurveyName=X.Surveyname and C.Interviewnumber=X.InterviewNumber
					and C.SMDTContactDate=X.SMDTContactDate	and C.ResponseStatus=X.ResponseStatus
			where
				 C.surveyname=@survey 
			order by ContactDate

	declare @logID bigint
	insert into dbo.impCatiLog(studid,beginn) select @survey,getDate()
	set @logID=@@identity
		set @isTran=1
		open cc3
		set @imported=0
		fetch next from cc3 into @lfd, @ConNr, @ConDate, @rc ,@IntNr, @AppTime, @dauer, @ContactID
		while @@fetch_status=0
		begin
			set @isLFD=0
			Select @isLFD=count(*) from i_data.sample where projid=@projid and lfd=@lfd
			if @isLFD = 1
			begin
				exec i_Flow.sp_Kontakt_INSERT @PROJID, @lfd, @einart, @survey, @konsource, @konArt, @konPers, @ConDate, @IntNr, @rc, NULL, @AppTime, @dauer, 'agent', @dt, 'agent', null
				
				update dbo.tmpCatiContactLog_Archiv  
					set toiSMS=1
				where ContactID=@ContactID
				
				set @imported=@imported+1
			end
			fetch next from cc3 into @lfd, @ConNr, @ConDate, @rc ,@IntNr,  @AppTime, @dauer, @ContactID
		end

		update dbo.impCatiLog set ende=getDate(),countOfData=@imported where id=@logID
		set @isTran=0
		close cc3  
		deallocate cc3

		fetch next from cc1 into @projid, @survey, @einart
	end

	close cc1
	deallocate cc1

[NeoPa]

The first thing that springs to mind is that the Agent is run under different credentials. Maybe the job is locking out early on and never progressing.

[BerndSX]

Thanks a lot for your reply !! :)

This is indeed true, but the job is run under the sa admin account ! So I wouldn't expect a problem in this direction.

I am watching a dependence on regarding the number of records in the inner cursor cc3.
The hangup occurs when then number of records starts exceeding 8.000 to 10.000 rows

[NeoPa]

I'm afraid I haven't had proper access to a SQL Server for over a year now so I can't help much from there. I would recommend tracing what's going on when the Agent runs it, but more detail than that I can't really help with :-(

[ck9663]

You might also want to cinsider not using a cursor.

Happy Coding!!!

~~ CK