Hi Naomi,

I have never used EXISTS operator and could not find much in the help file. Also, I have never used the DELETE ...FROM command either! Not sure why I never picked this up over the releases (have used INSERT INTO though). So I will have to give them a whirl. Can you give me the thinking behind "EXISTS" ... how does it differ from "IN"?

Albert

>Hi Albert,
>
>You can also add referential integrity rule so the child records would be deleted automatically when you delete the parent.
>
>Also as I said, you may combine select + delete into one delete command if your application logic allows this.
>
>BTW, you forgot to test EXISTS solution.
>
>select * from myTable where exists (select 1 from my Cursor)
>
>>Hi Sergey and Naomi,
>>
>>I decided to do some speed tests where I delete 3 "quotes" (3 header recs) and their child recs (14 recs) using the various ways for the view syntax. Here are the results (note that I did not do multiple tests but did close VFP after each test):
>>
>>(these all were 3 separate requeries/deletes/tableupdates as this is how the code now does it)
>>
>>- using subquery on another cursor: 7.953 secs
>>- using JOIN to the other cursor: 6.718
>>- using a query against a memvar: 3.031
>>
>>Then I threw more times into an array so I could see if one of the lines of code was slower than the others; it was then that I noticed that deleting the child recs was the slowest part of the whole thing. Checking into that, I found that there was no tag on the primary key (which should have shown up on other operations but no one has complained).
>>
>>So I re-ran the tests after adding the tag on the primary key:
>>
>>- using subquery on another cursor: 1.281 ses
>>- using JOIN to the other cursor: 0.032 secs
>>- using a query against a memvar: 0.015 secs
>>
>>Above though was still 3 different sets of operations within a loop (I think the loop was done originally so the user could be updated with the progress after each quote was deleted because it was so slow).
>>
>>So I changed the code to see if we could bring in all the recs to the parent/child views and see if that was faster:
>>
>>- using subquery on another cursor: 0.532 secs (faster than 3 loops which makes sense)
>>- using JOIN to another cursor: 0.016 secs (which shows that JOINs are indeed faster)
>>- using a query against a memvar: N/A (because 1 memvar, unless I wanted to stuff the Quote_ID's into a string?)
>>
>>So overall, obviously I need to add the tag on the child table and then if I want to keep it simple, changing the view code from being a subquery to being a JOIN would be the fastest code wise (other than the time taken to run all these tests!).
>>
>>Thanks for your help.
>>
>>Albert Gostick