Best Practice to Delete a Record 7

[ToddWW]

Using VFP 7.0 with CDX and a database container.

The database is sitting on a remote server on the network and the DBF is 100MB and the CDX is 100MB.

I want to delete a record, however there is a 50/50 chance that the record I want to delete does not exist. I have come up with a few ideas and I am looking for what might be the quickest solution.

Tablename: alertdvcfail
Index Tag: sd1_id
Index Expression: ALLTRIM(sd1_id)

Option #1

OPEN DATABASE databasename
DELETE FROM alertdvcfail WHERE ALLTRIM(sd1_id) == 'somedata'

Option #2

USE alertdvcfail ORDER sd1_id SHARED
IF SEEK('somedata')
  DELETE
ENDIF

What do you think ?

ToddWW

 

[rob444]

In my opinion option #2 is always faster.

Rob.

 

[cricket]

For a once-off (or infrequent)clean up, I would go for option #1, as you may be able to avoid issues with exact matching (UPPER and ALLTRIM) in indices.

 

[ToddWW]

This will be running very frequently. Thanks for the input guys. Cricket, I'll keep your suggestion in mind when I need to do infrequent cleanup procedures.

ToddWW

 

[justamistere]

Just a suggestion, to avoid some possible issues, and to expand on previous suggestions above.

I would save settings, lcOLDExact = SET("EXACT"), then "SET EXACT ON", and afterwards reset it, "SET EXACT &lcOLDExact".

I would index on index on UPPER(ALLTRIM(MyDataField)) and SEEK on UPPER(ALLTRIM(MySearchField)), if it is a Character type field.

 

[jimoo]

The problem option 1 presents is that it only deletes the 1st record that matches that key.

The problem option 2 presents is that it read the whole table.

Another option, if you are deleting more than one record with the same data value (i.e. Department code).

lcDeptCode = "020"
SELECT myTable
SEEK lcDeptCode && assumes index is set

DO WHILE NOT myTable.deptCode = lcDeptCode and !EOF()
DELETE
SKIP
ENDDO

Note: Intstead of DOWHILE - You can also use scan/endscan. It is usually a tad bit faster.

Especially on large tables, seek and dowhile or scan/endscan will almost always outperform a where clause.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[dbMark]

It is not wise to use ALLTRIM() in an index expression. The index structure should always be of consistent length. Using ALLTRIM() makes the index expression unreliable since the result is of unknown length. You should always write index expressions so they are of consistent length.

If not searching using the entire index expression, then it is better to adjust the EXACT and/or NEAR status when using SEEK() to locate what you need.

Have you tried using the SET KEY TO command? It is the index version of the SET FILTER TO command, but uses the index so it's faster. It also allows the use of a range. Don't forget to remove the key when you're done with it. One drawback in the old FoxPro from 10 years ago with SET KEY TO was that you didn't have a way to programatically detect with SET() whether a key had been set on a table. I don't know if VFP has resolved that and I don't have a copy at hand to test right now (close to midnight here). It is not KEY() since that is used to get the tag expression.

 

[SGLong]

dbMark,

Your suggestion about SET KEY was a real gem. I had never seen that command before so I thought I'd do a little test. I opened a file that contains 247,230 records, and made a note of the time. I then set the order to one of the date fields, set the filter to a specific date, did a GO TOP and then got the time value again - 124.688 seconds. Then I reran the routine but instead of setting the filter I did a SET KEY for the specific date, did a GO TOP and then got the time value of 0.219 seconds. That's amazing. It looks like I'll be looking thru my code doing some optimizations.

Steve

 

[jimoo]

Steve,

That advice is sound, but I still think you will find the performance from my suggestion even faster than the set key. I could be wrong, but I don't think I am. I have tried both methods in the past and the seek/DO While key =key and not eof (or scan/endscan) have always produced faster results in my testing.


Admittedly, much of it will depend on your environment, and how you have the data/indexes setup.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[skoko]

I cant beleive I never used SET KEY TO

dbMark, star from me.

 

[dbMark]

Thanks for the stars. I really appreciate it. Makes up for all my other bonehead ideas, I hope. And I'm glad we were able to help ToddWW, too. I learned to use SET KEY back in the late 90's when I was hired to maintain some existing FPD and FPW systems. Just when I had all the quirks and bugs worked out, the new VB/SQLserver system replaced it.

 

[ToddWW]

Hey Mark,

You guys did help me a bunch. I'm coming off of an old vfp3.0 application over to a vfp7.0 application. My biggest fan was the SET FILTER TO and LOCATE FOR methods for searching for records in very large tables. Sure, I was using CDX and Rushmore, but not until I met you guys did I learn about SEEK and SET KEY TO and SET EXACT. Wow, what a difference !! Thank you guys very much. You have really helped me advance this new project in directions that I never thought possible. Thank God that I found out in enough time to do some redesign to my tables and indexes before it was too late.

Thanks again. Look forward to working with you guys in the future.

ToddWW

 

[Olaf Doschke]

My 2 cents:
With SET KEY TO eExpression you could do DELETE ALL and it would just delete the records matching eExpression, but SET KEY TO is only nice when records matching that key are close together in the table and not widely spread. SET KEY TO can also take minutes, if the records are widely spread, so the following code snippet would still be faster and fast enough in any situation:

SELECT <tablealias>
SET ORDER TO <tagname>
IF SEEK(<eExpression>)
   SCAN REST WHILE <key> = <eExpression>
      DELETE
   ENDSCAN
ENDIF

But if you know about the typical spread or clustering of values in fields of a table, then SET KEY TO is very nice for performance tuning, that's right.

Just another hint:
There are situations, when certain keys are seldom and you're especially interested in these, to generate a filtered index, although that's not used for rushmore optimizing, and it's suggested to not use these, it could speed up SET KEY TO and SEEKs.

Bye, Olaf.

 

[jimoo]

What this forum demonstrates is (as with so many other things with VFP) that there are many ways to do it.

The key is to test them out in your environment. If your table is local and/or only contains a few records, it may not make much of a difference. However, if the data is stored on a server or network, or there is a lot of data, the performance can vary considerably.




Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com