Results 1 to 3 of 3

Thread: cursor to slow

  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: cursor to slow

    Is there a way to speed up this query using two cursors? I need the result set from the history cursor to change on each pass of the loop. I tried to just open and close the cursor each loop but it didn't work so I delclared it and dealocated each loop, which works but at a very slow pace. Is there a command to refresh the result set? or a way to do this as a batch?


    set nocount on
    Declare @entry bigint, @mobile int, @fid int, @rid int, @pin char(10), @commission tinyint,
    @barred tinyint, @barred_acse tinyint, @les char(10), @mobile1 int, @fid1 int,
    @rid1 int, @pin1 char(10), @commission1 tinyint, @barred1 tinyint, @barred_acse1 tinyint,
    @les1 char(10)

    DECLARE changes CURSOR
    FOR
    select mobile, fid, rid, pin, commission, barred, barred_acse, les
    from mobile_changes
    OPEN changes
    FETCH NEXT from changes
    INTO @mobile, @fid, @rid, @pin, @commission, @barred, @barred_acse, @les

    WHILE @@FETCH_STATUS = 0
    BEGIN

    DECLARE history CURSOR
    FOR
    select max(entry), mobile, fid, rid, pin, commission, barred, barred_acse, les
    from mobile_history
    where mobile = @mobile
    group by mobile, fid, rid, pin, commission, barred, barred_acse, les
    OPEN history
    FETCH NEXT FROM history
    INTO @entry, @mobile1, @fid1, @rid1, @pin1, @commission1, @barred1, @barred_acse1, @les1

    IF (
    @mobile = @mobile1 and @fid = @fid1 and @rid = @rid1 and @pin = @pin1 and
    @commission = @commission1 and @barred = @barred1 and @barred_acse = @barred_acse1 and
    @les = @les1)
    BEGIN
    DELETE FROM mobile_changes where mobile = @mobile
    END

    CLOSE history
    deallocate history

    FETCH NEXT FROM changes
    INTO @mobile, @fid, @rid, @pin, @commission, @barred, @barred_acse, @les
    END

    close changes
    deallocate changes

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    To speed up your processing, you can avoid the cursors. Use temp table instead.

    Try the following steps:

    1. Create temp table from mobile_history with all required fields.
    select max(a.entry) MaxEntry, a.mobile, a.fid, a.rid,
    a.pin, a.commission, a.barred, a.barred_acse, a.les
    into #temp_history
    from mobile_history a, mobile_changes b
    where a.mobile = b.mobile
    group by mobile, fid, rid, pin, commission, barred, barred_acse, les

    2. Delete records from the table mobile_changes using temp table.
    delete mobile_changes
    from mobile_changes a, #temp_history b
    where a.mobile_no = b.mobile_no
    and a.fid = b.fid
    and a.rid = b.rid
    and a.pin = b.pin
    and a.commission = b.commission
    and a.barred = b.barred
    and a.barred_acse = b.barred_acse
    and a.les = b.les

    The above will do the same thing. Here, my only confusion is why do you need max(entry) in mobile_history table. Anyway, you are not using it anywhere. If i assume that u are not using it anywhere, our query can be even simpler. Just use the following query.

    delete mobile_changes
    from mobile_changes a, #mobile_history b
    where a.mobile_no = b.mobile_no
    and a.fid = b.fid
    and a.rid = b.rid
    and a.pin = b.pin
    and a.commission = b.commission
    and a.barred = b.barred
    and a.barred_acse = b.barred_acse
    and a.les = b.les

    Thanks.
    Prabhanjan

  3. #3
    Join Date
    Oct 2003
    Posts
    24
    The Entry field is a auto numbered primary key. The max(entry) returns the latest record grouped by mobile. Thanks for the help the cursor method was taking up to 50 minutes to complete, your method takes < 10 seconds.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •