Simplicty and flexibility!

DataEase 8.5 - New Function: IndexUpdate() - Dramatically increase speed! (Ver.

Started by DataEase
You will need to Sign In to be able to comment on the Blog!

DataEase 8.5 - New Function: IndexUpdate() - Dramatically increase speed! (Ver.

IndexUpdate (8.5 onwards)

RetVal := IndexUpdate("Quick") .

DataEase in "default" mode will update index after each record modification. This insure consistency and immediate "correct" index but when one run a big update this will be stealing a lot of processing power and will be the main reason for slow performance.

IndexUpdate function has to main purposes:
1. For developers to insure that a index is consistent before a major update/transaction so records are not left out without having to resort to reorganizing a table.
2. For developers to temporary disable index updating for big transactions to speed up performance and then simply build the index at the end of the transaction.

IndexUpdate() has three modes:

On (Full): Default
This mode will update the index after each updated row.
This mode will update the index for each transaction. A transaction is a update of one table (relationship). DO NOT USE THIS MODE IF YOU ARE DOING NESTED UPDATES/DELETES/INSERTS!
This is the ADVANCED (Manual) mode. If you use this the index will not be updated at all. This will leave the index "wrong" until you manually update it with IndexUpdate("YourTableName").
This is the best mode to use if you are doing heavy nested transactions. Just make sure you update manually the indexes for all tables involved in the transaction.

NB! When you have used any other mode then ON(Full) you need to revert to ON at the end of your DQL if not all DQLs in your session will keep on running in the last used mode.



ON|QUICK|OFF - These switches will set the mode of IndexUpdating in DQLs from her until you change it. This is global.

TableName - The name of the table you want to update the index on. It will update all Index for this table.




Example 1:
This sample will simply update the index before doing a transaction, then switch it off and then updated it again:

define "startTime" time .

define "retval" text .

define "counter" number.

counter := 0 .
retval := IndexUpdate("SomeData") . -- Update the index to insure it is current. This is normally not necessary.

retval := IndexUpdate("Off) . -- Switch off Index Updating from her..

startTime := current time .

For SomeData ;

modify records in SomeData named "Drozd" with RecordNr = SomeData RecordNr

SomeText := concat(counter," This is changed!" ).

counter := counter + 1 .


retval :=IndexUpdate("On") . -- Switch index updating back to normal.

retval := IndexUpdate("SomeData") . -- Manually update the index.

message concat("Modify took : ", current time-starttime, " seconds.") window .

This DQL on a table of 100.000 records ran 111 times faster than the same DQL in 8.2 and 12.5 times faster then the same DQL in 8.5 Default mode. This DQL took 24 minutes in 8.2, 2 minutes in 8.5 Normal and 13 SECONDS in 8.5 Afterburner.

If you used Quick mode on this DQL you would basically KILL your computer. IndexUpdate on this table took 8 seconds (so the change took only 5!), but if you had used quick you would have gotten 100.000 x 8 seconds, so it would have taken 92 DAYS. This just to illustrate when to use On (Normal), Quick, and Off/Manual.

Example 2:
This sample will showcase quick, which is designed to speed up linear transactions i.e. big updates/insert/deletes into one table with one With statement.

define "startTime" time .

define "retval" text .

startTime := current time .

for SomeData with SomeText = data-entry field1 ;

delete records .

endretval := IndexUpdate("On") .

message concat("Delete took : ", current time-starttime, " seconds.") window .

retval := formclear() .

This DQL took 1 second on 100.000 records, when the Full (Default) mode took 37 sec. In reference DE8.2 would take 132 seconds.

Quick was designed as an easy way of controlling speed for big linear transactions and it is amazing when used right, but as it is easy to use it wrong (as illustrated in example 1) you might want to go fully manual and use Off/IndexUpdate(TableName) which will give the best performance overall.

Written by DataEase 31/01/15 at 12:57:56 Dataease [{8}]FIVE