v5.6 Builds Index of String/VarChar field in 10-15 times faster!

Bench Description

Prepare Steps

  • We have table T1 with field String[120] or VarChar.
  • We add 1 million records with unique values using METHOD( ‘concat( rand_string(80), RecID’ ) ) 

 So we get table with one only field, 1 millions records, values are about 80-90 chars length.


  • Build index for this field.

Continue reading v5.6 Builds Index of String/VarChar field in 10-15 times faster!





The results were more than impressive.


Read details on  Valentina WIKI.

New Index Optimization Makes v5.4 up to x3 times faster of v5.3

Description of Bench

Prepare Steps:




Each search returns 1 found record.


Bench Hardware

MacBook Pro 2.2 GHz Intel Core i7 (Early 2011),
RAM 8GB 1333 MHz,


Bench using v5.3

Count = 10 000 000 Opt1 = 1 Opt2 = 0

ADD inCount Records = 5 450 ms = 5.45 sec
Build Index f1 = 1 877 ms

Count searches FORWARD = 30 310 ms
Count searches REVERSE   = 28 821 ms
Count searches 2 directions = 85 018 ms


Bench using v5.4

Count = 10 000 000 Opt1 = 0 Opt2 = 0
No optimizations. We see pure speed of BinarySearch.

ADD inCount Records = 4 543 ms
Build Index f1 = 1 922 ms

Count searches FORWARD = 124220 ms
Count searches REVERSE = 128918 ms
Count searches 2 directions = 134173 ms


Count = 10000000 Opt1 = 1 Opt2 = 0

Enabled Opt1, i.e. Optimisation using the current page.

ADD inCount Records = 4 458 ms
Build Index f1 = 1 823 ms

Count searches FORWARD = 19 849 ms
Count searches REVERSE = 20 848 ms
Count searches 2 directions = 74 005 ms


Count = 10000000 Opt1 = 0 Opt2 = 1

This combination have no many sense. Just for interest.
We see that times for all 3 benches becomes good,
but forward and reverse are not so fast as with Opt1.

ADD inCount Records = 4508 ms
Build Index f1 = 1715 ms

Count searches FORWARD = 23 951 ms
Count searches REVERSE = 24 875 ms
Count searches 2 directions = 28 200 ms


Count = 10000000 Opt1 = 1 Opt2 = 1

ALL optimizations ENABLED.
We see that again forward and reverse have the best times AND

ADD inCount Records = 4 373 ms
Build Index f1 = 1 729 ms

Count searches FORWARD = 20 950 ms
Count searches REVERSE = 19 002 ms
Count searches 2 directions = 24 981 ms


Join speed improved

After some benches we have discover that loop on small joins (when only one record on the left and few on the right) is not fast enough.

We have two major algorithms internally and we have discover that the first takes only 50 seconds on 10000 loops, while the second takes hundreds of seconds…So problem presents in the second algorithm.


Now the second algorithm takes 120 second. And we can add yet choice condition to choose the first for such case of small joins…

I think we will be able to improve the second alg yet to 70-80 sec. And may be with more complex changes it is possible to speed both after that …

VClient and VServer now can analyze network

After some benches we have discover that it is not a good idea to use ZIP compression of data if VServer is on localhost or on fast LAN.

To resolve this we have specify 3 possible steps and made the first one without changes in API. As result, now VClient and VServer automatically can do raw analyze of network and define if to use ZIP.

Future we will add even special VClient function VConnection.Tune(), which your app can call on start, and later each 30 min for example to measure and analyze network state more accurately.