Hi Sergey,

I get a failure when trying to create the view programmatically as the "c_QuotesToDelete" is a cursor which doesn't exist at the time the view is created. And so this command fails:

CREATE SQL VIEW v_PartFileQuoteHeaders AS ;
   SELECT ;
      Quotes.Quote_ID ;
      FROM Quotes JOIN c_QuotesToDelete ON Quotes.Quote_ID == c_QuotesToDelete.Quote_ID

Albert

>Hi Albert,
>
>Unfortunately, VFP doesn't show level of optimization when subqueries or correlated queries are used. Anyway, JOIN is usually faster in VFP. You may also try a correlated subquery but I doubt it'll be faster.
>
>

>* JOIN
>SELECT  qt.Quote_ID ;
>   FROM Quotes qt ;
>   JOIN c_QuotesToDelete qtd ON qt.Quote_ID = qtd.Quote_ID
>
>* Correlated subquery
>SELECT qt.Quote_ID ;
>   FROM Quotes qt ;
>   WHERE EXISTS (SELECT * FROM c_QuotesToDelete qtd WHERE qt.Quote_ID = qtd.Quote_ID)
>

>>
>>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