May 16, 2003 at 10:13 am
i am trying to update data between two servers. there are only 2 tables involved but it is running so slooooooooooow that i cant finish the update in time. please help
here is what i have
declare @patkey as varchar(30)
declare @index as int
declare @dts as smalldatetime
declare @i as int
declare @TempIndex as int
set @i = 0
set @TempIndex = 0
declare crPatkeys cursor for
select patkey,questdat from nt1.arc.dbo.demographics where databasename='report' and questid=1010
open crPatkeys
fetch next from crPatkeys into @patkey,@dts
while (@@fetch_status <> -1)
begin
declare crQIDHigh cursor for
select ArcQuestid,[index] from report where patkey = (select patkey from rpfixed where natpat = cast(@patkey as varchar(30))) and year(visitdate) = year(@dts) and month(visitdate) = month(@dts) and day(visitdate) = day(@dts) and arcsync = 1
open crQIDHigh
fetch next from crQIDHigh into @i,@index
if(@@Fetch_status =-1)
begin
select @patkey,@dts,@i,@index
end
if(@@Fetch_status <>-1)
begin
update nt1.arc.dbo.demographics set questid = @i where databasename='report' and year(questdat) = year(@dts) and month(questdat) = month(@dts) and day(questdat) = day(@dts) and patkey = @patkey
end
close crQIDHigh
deallocate crQIDHigh
fetch next from crPatkeys into @patkey,@dts
end
close crPatkeys
deallocate crPatkeys
May 16, 2003 at 11:32 am
It looks like you are not selecting into all your first cursor variables when you find records in the second cursor. The variables @patkey, @dts, @index, and @i will remain the same, so it will just continue to execute the same statements over anad over. Perhaps there is a way of doing this update without cursors. Could you clearly state the business requirement, and maybe we could help with a set-based solution...
HTH,
Jay
EDIT: Sorry, didn't realize you did NOT have a while loop in your second nested cursor. so, the cursor variables should work ok. Still, I would print statement out all the variables to double-check if they are what you expect. I would still prefer a set-based solution, however, so if possible, could you post the problem you're trying to solve with the cursors...
Edited by - jpipes on 05/16/2003 11:34:46 AM
May 16, 2003 at 1:30 pm
As the last poster noted, it appears you don't want to update using every possible match in reports, only the first one the cursor finds. If that's what you intended, then this won't help. However, this would be a set-based solution if all (or any) matching report record is acceptable:
update demo2
set demo2.questid = r.ArcQuestid
from nt1.arc.dbo.demographics demo2
INNER JOIN nt1.arc.dbo.demographics demo1
ON ( demo1.questid = 1010
AND demo1.patkey = demo2.patkey
AND demo1.databasename = 'report'
AND demo2.databasename = 'report'
AND year(demo1.questdat) = year(demo2.questdat)
AND month(demo1.questdat) = month(demo2.questdat)
AND day(demo1.questdat) = day(demo2.questdat)
)
INNER JOIN rpfixed rp ON rp.natpat = demo1.patkey
INNER JOIN report r ON (r.patkey = rp.natpat AND r.arcsync = 1)
where year(r.visitdate) = year(demo1.questdat)
AND month(r.visitdate) = month(demo1.questdat)
AND day(r.visitdate) = day(demo1.questdat)
Since I don't have your tables, this might not even be runnable
Still, it should come a step or two closer to a set-based implementation.
Hope it's some help.
RD Francis
R David Francis
May 16, 2003 at 1:32 pm
Note that, if any of the matching records from the report table could be used to update the demographics table, then the solution I present should still work; however, if your data is stored in such a way as to make the first record returned by the cursor the one that has the value you need, you'll still need another solution.
RD Francis
R David Francis
May 17, 2003 at 10:57 pm
Agree drop the cursors if possible.
May 19, 2003 at 2:17 am
I am not really sure but are you using a Linked/remote server here......
If so make sure the codepages are the same and make sure you set this correct in the linked server properties.
Cause if you don't all the data will first be converted.... ( that is right) on the local server and only then will the query be executed. But if the server knows the codepages are the same it will execute the query directly and remote........
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply