Index on Deleted() VS .NOT. Deleted()

Index on Deleted() VS .NOT. Deleted()

• VandB

  Probably neither of them.

  Check fox.wikis.com.

  
  

• sharpdotnet2005

  intel4:

  That is a very tricky question. Both Stuart and Cetin are correct in their answers. Deleted() is better than NOT Deleted() as the latter is not optimizable in prior versions.

  But sometimes an index on DELETED() hinders rather than helps. Many years ago, a developer (Chris Probst) researched this issue and found the problem. He published a very good article about it in ForxPro Advisor magazine (May 1999).

  The issue is summarized in the following article in the Fox Wiki (that Cetin aluded to):

  http://fox.wikis.com/wc.dll Wiki~NonDiscriminatingIndex

  As Stuart said, see also: http://advisor.com/doc/09237

  Please read it and understand the issues involved before you rely on an index on DELETED(). If you have any specific questions after your testing is done come back and we'll try to help.

   

  
  

• rkorneev

  Seems to be a good time for a wakeup call here:

  VFP9 introduced a new index type, called BINARY. Guess what: This one is especially targeted for the DELETED() index. Instead of storeing 5 bytes per record, this index uses 1 Bit per record, which makes the final index size 1/30th of the old one.

  Thus: With VFP9 it's always a good thing (tm) to add this one to all your tables:

  INDEX ON DELETED() TAG _DelFlag BINARY

  Gotchas: You can't access those tables with anything else than VFP9, and this also rules out ODBC access (since MS didn't updated those drivers since VFP6)

  wOOdy

  
  
  

• Iker Garaizabal

  In VFP9 with "set deleted on"

  i think "index on deleted() tag del binary" is good choice. Ok

  Javid

  
  
  

• ultrabit79

  Hi
  
  If you're using VFP8 or earlier you have to use the former. From VFP8 help:
  
  "Rushmore cannot use an index created with a NOT condition. For example, the following expression can be optimized:

  INDEX ON DELETED() TAG DEL

  But this one cannot:

  INDEX ON NOT DELETED() TAG NOTDEL"
  
  

  If you're using VFP9 "INDEX ON NOT(DELETED()) optimizes NOT(DELETED()) and DELETED() query conditions" and you can use a Binary index - see http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_foxhelp9/html/7ecc3532-d06f-42b4-b1d0-9bed677cec90.asp for more. That document also mentions the performance problems indexes like this can cause on large tables in a LAN environment.

  Edit - see also http://advisor.com/doc/09237
  

  Regards

  Stuart Dunkeld

  
  
  
  

• Compugab

  Right wOOdy. I neglected to mention BINARY.

  It is always a good thing (tm) when we have a better solution in VFP! :)

   

  
  

• DotKu

  As I understand it, even with binary, it's not always a good idea to create the tag. What binary does is change the equation as to whether it's a good idea.
  
  The basic issue is that sometimes, reading the part of the index corresponding to NOT DELETED() is slower than reading all the records that match other conditions and checking them sequentially for deletion. With the binary tag, the number of records at which that becomes true is much higher.
  
  Tamar
  
  
• Index on Deleted() VS .NOT. Deleted()