VERY slow running cursor

  • 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

  • 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

  • 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

  • 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

  • Agree drop the cursors if possible.

  • 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