Knowledge Base

Hint Ref: 021201300005
Hint Date: 30/01/2012

Hint Details:

PROBLEM

A user had a database function which, for each record in a cursor query, inserted a new record.

When the cursor query was executed on its own, it completed in under a second. However, the procedure, when run, took forever to complete.

The Function went something like this

YOU MUST NOT TRY TO RUN THIS ACTUAL PROCEDURE - IT IS ONLY AS AN EXAMPLE!

create procedure spaceman.newproc1()
begin
 for subloop as curs1 dynamic scroll cursor for
   select field1 as w,field2 as x, field3 as y, field4 as z from subcontract b key join movement m where b.subcontracttype = 'I' and b.leavedate is null do
   update movement set endstatus = 'Vacated In Rentplus' where movementid = x;
   insert into movement(movementid,subcontractid,contractid,movementstart,startstatus,
     quantity,userid,unitid,ledgeritemid) values(uniquekey(x),y,w,current date,'Vacated In Rentplus',0,'Admin',z,v) end for
end

ISSUE

The issue is that the query on which the cursor is based is affected dynamically by the code within the cursor itself. In other words the cursor query is increasing in size as a result of processing an insert statement within the cursor loop!

Imagine, if you will, a loop which says, 'for every record in the table that meets a specific criteria, add a new record to the table which also meets the same criteria' - the loop would go on forever!

SOLUTION

Because the movement record being added is, in this case, going to be the only one with a startstatus of 'Vacated in RentPlus', I can simply amended the cursor query by adding the following to the where statement:

and startstatus <> 'Vacated In Rentplus'

However, what to do if there is no obvious way to exclude the inserted records from the cursor query?

Here the answer is to use a temporary table thus:

create procedure spaceman.newproc1()
begin
  select field1 as w,field2 as x, field3 as y, field4 as z into #newproc1temp from subcontract b key join movement m where b.subcontracttype = 'I' and b.leavedate is null;
 for subloop as curs1 dynamic scroll cursor for
   select w, x, y, z from #newproc1temp do
   update movement set endstatus = 'Vacated In Rentplus' where movementid = x;
   insert into movement(movementid,subcontractid,contractid,movementstart,startstatus,
     quantity,userid,unitid,ledgeritemid) values(uniquekey(x),y,w,current date,'Vacated In Rentplus',0,'Admin',z,v) end for;
  drop table #newproc1temp;
end;

In this case the records in the temporary table cannot be affected by the addition of records to the movement table.

(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)