take a long time to run a fetch statement

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)

take a long time to run a fetch statement

Author

Topic

sunpalozzi
Starting Member

9 Posts

Posted - 2005-12-19 : 13:43:58

hello all,
I have been bugging this script for a few days. For some reason this fetch statement takes a long time run :
open DOC1099
>>fetch DOC1099 into @ref
any idea why. thanks.

Sunpalozzi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-19 : 14:18:18

<sarcasm>
I've spelled a word incorrectly in my application for a DBA position. Here is the letter that's wrong:

>>e

any idea what the correct letter is?
</sarcasm>

Do you really believe you've provided enough information for someone to tell you what's wrong?

Be One with the Optimizer
TG

sunpalozzi
Starting Member

9 Posts

Posted - 2005-12-19 : 14:30:16

Sorry about that. Thank you for pointing that out. Here is my code:
DECLARE @vend VARCHAR(50),
@Dflt varchar(2),
@ref varchar(50),
@TOTAL_REC Int,
@doc Int

if @vend = ''
DECLARE Vendors CURSOR FOR
SELECT vendid, DfltBox
FROM Vendor
WHERE rtrim(ltrim(vend1099)) ='1'
order by apacct
else
DECLARE Vendors CURSOR FOR
SELECT vendid, DfltBox
FROM Vendor
WHERE rtrim(ltrim(vend1099)) ='1' and
vendid = @vend
order by apacct
OPEN Vendors
FETCH Vendors INTO @vend, @Dflt
WHILE (@@FETCH_STATUS = 0) BEGIN
DECLARE DOC1099 CURSOR FOR
SELECT refnbr from apdoc where vendid = ltrim(rtrim(@vend)) order by refnbr
open DOC1099
--the follow line takes a long time to run
fetch DOC1099 into @ref
while (@@FETCH_STATUS = 0) begin
update aptran set Boxnbr = @Dflt where refnbr = ltrim(rtrim(@ref)) and linetype = 'N'
fetch next from DOC1099 into @ref
end
deallocate DOC1099
FETCH Next from Vendors INTO @vend, @Dflt
end
deallocate vendors

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-19 : 14:55:37

no problem, sorry for the sarcasm

You really need to start to think about sql server as a set based tool rather than a procedurual one. I think this will do the same thing as your two nested cursors. But it will work in one quick statement instead of iterating through (locked) resources and updating rows one at a time.


declare	@vendid varchar(50)

update	atr set
	atr.Boxnbr = v.DfltBox
from	Vendor v
join	apdoc ad
		on ad.vendid = v.ltrim(rtrim(vendid))
join	aptran atr
		on atr.refnbr = ad.refnbr
		and linetype = 'N'
where	rtrim(ltrim(v.vend1099)) ='1' 
and	vendid = 
		case 
		when @vendid = '' then vendid 
		else @vendid 
		end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources