General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only