Hi all,

I wrote a new delete routine that uses a view to delete multiple records. The view uses a subquery to get the ID numbers of the records (quotes) that the user has checked for deletion.

1) I have a simple cursor that is populated with the ID numbers of the quotes records to be deleted e.g.

CREATE CURSOR c_QuotesToDelete ( Quote_ID N(6), Description C(35), DeletedOkay L(1) )

This is then stuffed with a series of ID's to delete (done this way to fit into the framework better).

The view is then created programmatically and it has the syntax:

SELECT ;
   Quote_ID ;
   FROM Quotes ;
   WHERE Quote_ID IN ( SELECT Quote_ID FROM c_QuotesToDelete )

I thought this would be fairly fast as I would be populating the view all at one time (a requery) and then deleting all the rows and then posting the change via a TABLEUPDATE(). A similar thing is done to the child records of each quote.

But it is quite slow (user "checks" 3 documents and it takes 7 seconds to run). I used SYS(3054,1) to try to see what was taking the time and it says that the view is not optimized at all. I have a tag on Quote_ID so I would have thought it should have been optimized.

I could go back to doing a loop and looking up each record via a SEEK but that seems like going backwards - and somehow I thought the view would overall process faster than doing mulitple steps of code.

Any ideas? Any way to test this further as to why not optimized?

Thanks,
Albert Gostick