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!