CODEWITHSUNDEEP
sqlsql-serverstored-procedurescursor
fortune
fortune

Reputation: 1567

SQL - Slow Cursor Simple Stored Procedure

I have a relatively simple stored procedure, but the amount of data in the transactions table is causing it to take forever to run. Any suggestions on a way I could either optimize the query or convert it to not use a cursor would be greatly appreciated. 

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ItemID uniqueidentifier 

SET @CurrentCount = 0;

DECLARE @TempTransTable TABLE
(
    ID uniqueidentifier
)

-- Insert statements for procedure here
DECLARE curs_GetAllItems CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
    select 
        ID
    from 
        item
    where 
        locationid in 
            (
                -- gets corona locations
                select 
                    Locations.ID
                from Locations
                    left outer join unit
                        on unit.locationid = locations.id
                where unit.unittype = '4'
            )
        and stat not in (1,10,11,13) -- only items not in stock

    OPEN curs_GetAllItems

    FETCH NEXT FROM curs_GetAllItems INTO @ItemID
        WHILE (@@FETCH_STATUS =0)
        BEGIN
            -- Clear table to ensure accurate data
            DELETE FROM @TempTransTable

            -- Insert transaction records to delete
            -- Every transaction except the first two and the last
            INSERT INTO @TempTransTable
            select 
                ID 
            from 
                transactions 
            where 
                transactions.id not in
                    (select ID from (select top 2 * from transactions where itemid = @ItemID order by transdate asc) as t1
                    union 
                    select ID from (select top 1 * from transactions where itemid = @ItemID order by transdate desc) as t2)
                and itemid = @ItemID

            -- Delete trans records
            DELETE FROM 
                dbo.transactions 
            WHERE 
                transactions.ID in (select ID from @TempTransTable);

        -- Get next item.id
        FETCH NEXT FROM curs_GetAllItems INTO @ItemID
        END
    CLOSE curs_GetAllItems
    DEALLOCATE curs_GetAllItems
END

Upvotes: 0

Views: 722

Answers (2)

burnall
burnall

Reputation: 842

I think you should review your approach. It should be possible to manage without cursor and loop at all. May be you need use intermediate temporary table (consider adding indexes to it).

Other point: if those queries are hard and they are working in database with concurrent changes, you can easy get into problems - locks, timeouts and inconsistent data.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

;with tmp as (
    select *,
           rn_asc = ROW_NUMBER() over (partition by t.itemid order by transdate asc),
           rn_desc = ROW_NUMBER() over (partition by t.itemid order by transdate desc)
      from transactions t
     where exists (
          select *
          from item i
          join Locations l on i.locationid = l.ID
          join unit u on u.locationid = l.id and u.unittype = '4'
         where i.id = t.itemid)
       and stat not in (1,10,11,13) -- only items not in stock
)
    delete tmp
     where rn_asc > 2 and rn_desc > 1;

Upvotes: 1

Related Questions