Insertion speed

Global insertion speed is a bit reduced by the uniqueness look-up of both new columns, as could be expected.
On-the-fly creation of the index and associated lookup at each row insertion has a performance cost.

You can take a look at the previous benchmark results, which does not have the two stored false fields definition, for comparison.

  SQLite3
(file full)
SQLite3
(file off)
SQLite3
(mem)
TObjectList
(static)
TObjectList
(virtual)
SQLite3
(ext file full)
SQLite3
(ext file off)
SQLite3
(ext mem)
Oracle Jet
Direct 7 328 21162 89355 89002 8 324 19137 475 617
Batch 8 330 24584 121365 126227 8 331 22235 31456 707
Trans 11029 22995 25282 89051 89118 10299 21980 22803 763 924
Batch Trans 11608 27848 29448 127805 129005 12017 25392 25795 22555 930

But the new hash-based index feature of TSQLRestServerStaticInMemory is working pretty well. There is no big impact at insertion time (around 10%), when compared to the previous benchmark.
Without the index, the insertion rate of the 5000 rows of data was only around 600 per second: the lookup of all existing values to ensure of value uniqueness was very time consuming.
And when compared to other databases, this index sounds to be very efficient. Oracle speed, for instance, decreased by around 40%, for batch mode insert.

Read speed

A new line, labeled "By name", has been added to the benchmark code.
It will query all rows of data, using a search by the LastName property.

  SQLite3
(file full)
SQLite3
(file off)
SQLite3
(mem)
TObjectList
(static)
TObjectList
(virtual)
SQLite3
(ext file full)
SQLite3
(ext file off)
SQLite3
(ext mem)
Oracle Jet
By one 10461 10549 44737 103577 103553 43367 44099 45220 901 1074
By name 9694 9651 32350 70534 60153 22785 22240 23055 889 1071
All Virtual 167095 162956 168651 253292 118203 97083 90592 94688 56639 52764
All Direct 167123 144250 168577 254284 256383 170794 165601 168856 88342 75999

As could be expected, the read speed was not affected by the newly defined stored false attribute.

The "By name" new search is pretty fast, similar to "By one" (i.e. by ID) lookup, much faster than a classic search: here, the underlying database index does wonders.

Comments are welcome on our forum!