Check also Re: Selecting FROM/INTO same cursor Thread #1336417 Message #1336747

You query is the same as

SELECT Q.Quote_ID FROM Quotes Q INNER JOIN c_QuotesToDelete  Del ON Q.Quote_ID = Del.Quote_ID

This should work much faster.



>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