[fpc-pascal] msedb and fcl-db benchmarks

[Joost van der Sluis]

Hi all,

To see what the differences in speed are between MSEdb and fcl-db I did
some benchmarks. (See the attachment for the code and results)

I think that the first conclusion is that when it comes to executing
queries, opening connections to the database and such, the 'real sql-
work' there's no real difference. Which was to be expected, since the
code in that areas is practically the same.

But as soon the TBufDataset is used in the area it's designed for, there
are differences. Also not that strange, since this is the part that is
rewritten in MSEdb.

If you use a large dataset, (100000 records, about 2020 bytes for each
record) simply opening the dataset and fetching all records is 7 times
slower with MSEdb. (OpenFetch test)

Further, when you 'scroll' through all records, and read-out all data,
MSEdb needs more time for every record. (ReadData test, see graph) 

The result is that if the dataset is opened, and scrolled from the begin
to the end and back again once, fcl-db is 6 times faster. If you do this
10 times, fcl-db is 'only' three times as fast. But the absolute time
difference is larger. 

If you open this same dataset, end change the first 20.000 records.
MSEdb is faster then fcl-db. (about 1.2 times) Thus you can conclude
that with editing large amount of datasets, msedb is faster, it so fast
that it overcomes the browse-slowlyness.

I think that the reason for the difference in editing is simple: to
spare memory fcl-db doesn't store the location of the edit-buffer with
each record-buffer. So it has to search the edit-buffer for every record
you want to change. I did that on purpose, since I thought that editing
this amount of records, without applying the changes, isn't something
that you do in a regular program.

If it comes to the use of RecNo, msedb beats fcl-db. Fetching the data
msedb is slower, offcourse. but then for each 'jump' it consumes almost
no time at all. (see JumprecNo graph, the first 'dot' is more a
randomness-thing if you use only 5 points)

But, on the other hand, if you use bookmarks to do the same thing. fcl-
db behaves just like msedb. Jumping through the records is fast.


I tried to explain the readdata-difference. At first I thought that
msedb converted all strings from string to widestring to string. But it
doesn't do that. (see my mails on this subject)

I think that the only reasonable explanation is the argument from
Michael: using ansistrings/pointers to store the data instead of storing
them directly into a buffer, induces so much overhead that it becomes
too slow.

Overall, I would conclude that the current design of the TBufDataset of
fcl-db is a better choice then the design of tmseBufDataset. Since fcl-
db is faster on those things a TBufDataset is build/meant for: browsing
through records.

Discussion: What tests could I do more? Is there something I overlooked?

Regards,
  Joost.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: bench.pas
Type: text/x-pascal
Size: 4602 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20070716/33fcf3b6/attachment.pas>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: db_benchmarks.ods
Type: application/vnd.oasis.opendocument.spreadsheet
Size: 19638 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20070716/33fcf3b6/attachment.ods>