Disable Indexes of Table

If you are going to make massive operations on the Table, e.g. to delete many records, add or import many records then it is a good idea to disable indexes of all table fields before massive changes return indexes back.

Imagine that you try to implement this task yourself. You should

• do loop by all fields of the table.
• if a field is indexed then disable indexing flag for it and store info about this into some array
• after massive operation again loop through all fields of the table and return back flags.

Also, it is important to implement this in the exception safe way for languages that have exceptions.

To help you with such task, Valentina offers VTable.IndexChanges property, which does all that job in 1-2 lines of code.

EXAMPLE

  tbl.IndexChanges = false
 
  try
  {
      // do some massive changes
  }
  catch
  {
      tbl.IndexChanges = true
  }
 
  tbl.IndexChanges = true

NOTE: VTable.IndexChanges does not clear flag VField.Indexed. It just trashes an index file physically. Thanks to this there is no need to save the state of flags. But then we get restrictions as described below. Maybe we will change this sometime…

NOTE: You may wonder what is a massive operation. There are no rules here. Everything depends on data, table schema, fields types and quantity and so on. So best of all is to have some own benches to see when this trick starts giving effect to you. For example, this can be 10% of records to be modified.

When you set this property to FALSE, the Table trashes all not unique indexes and remembers this state in the flag. So the field still has flag Indexed, but index file itself trashed. From now, insert/delete/update operations will not spend time on that indexes. Unique index(es) of the Table (if any exists) still will be maintained.

When you set this property back to TRUE, the Table clears the state in the flag and force Reindex of all dropped indexes.

Currently, only insert/delete operations respect this internal flag of Table. This means that while indexes are disabled, you should do only these operations!

If you will try, for example, to do SELECT or API search on a field with disabled index, then the engine will see that field has flag Indexed, but there is no index file on the disk, then index will be built for that search. The problem is that future inserts/deletes will not go into this index because Insert/Delete algorithms still think that table is in the Disable Indexing mode … This problem will become visible on the next search.

Later IndexChanges = true will fix indexes anyway, by reindex.

You should use this feature very carefully with Valentina Server. Because other users will not respect this flag and force automatic rebuild indexes during their work.

Best of all, use this only as admin and only for admin tasks.

Currently, Valentina engine uses this feature in the following places:

* VDatabase.Clone - we create new db and copy all existed records 100%, so it is wise to disable indexes while all records are copied.

* Import Text - if import file size is bigger than 5% of table size (including indexes), we consider this as massive operation.

Anywhere you see that your application do a massive operation on Table records. For example, for Valentina engine, it is not possible to estimate labor of DELETE FROM T WHERE … in the case when present cascade deletions are in related tables. But you may know that info in your application. So maybe you can find useful disable indexes for related T2 and T3, then delete records, then return indexes back.

Best of all, consider this feature as helper tool for massive admin operations.