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.

[...]

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)

You are wrong here.
"
function tmsebufdataset.loadbuffer(var buffer: recheaderty): tgetresult;
[...]
     if field1.datatype in charfields then begin
      int2:= int2*4+4; //room for multibyte encodings
      setlength(str1,int2);
      if not loadfield(field1,pointer(str1),int2) then begin
       setfieldisnull(buffer.fielddata.nullmask,fno);
      end
      else begin
       if int2 < 0 then begin //buffer to small
        int2:= -int2;
        setlength(str1,int2);
        loadfield(field1,pointer(str1),int2);
       end;
       setlength(str1,int2);
       po2:= pointer(@buffer) + offset;
       if bs_utf8 in fbstate then begin
        po2^:= utf8tostring(str1);     <<<<<<<<<<<<<<<<----
       end
       else begin
        po2^:= msestring(str1);     <<<<<<<<<<<<<<<<----
       end;
"
msestrings.utf8tostring:
"
function utf8tostring(const value: utf8string): msestring; overload;
"
msestring = widestring.

tmsebufdataset is designed to work well with the MSEgui visual components where TBufDataset was not ideally suited.

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

To work with tmsqlquery you should use tmsestringfield.asmsestring and not TField.AsString, otherwise the slow ansistring<->widestring conversion takes place on every access. On Windows you should not use FPC 2.1.4+ because widestrings are no more reference counted and heap based...

Martin

How about memory usage ? That is my main concern.

Michael.

Why not ? From what I can see, your only problem is the widestring issue, which we can solve as indicated in earlier mails. The reference count issue has nothing to do with TBufDataset...

My conclusion is that there is no reason why we should not merge the 2 implementations - provided we include the possibility for you to store everything as widestrings, of course, and this should not be a problem.

Since 2.0.4 will no longer be supported, this is something you will have to take into account. As far as I know, the reason for not having widestrings reference counted on Windows is that they must be COM compatible.

But first we should still check memory requirements.

Michael.

Ah, that could explain the long fetch-times. I'll investigate this.

I'll change that. And I'm unsing linux.

Regards,
Joost.

If you know a way to measure that using linux? It should be possible, the shootout ppl also do this.

Joost

Joost van der Sluis wrote

If you know a way to measure that using linux? It should be possible, the shootout ppl also do this.

http://shootout.alioth.debian.org/gp4/faq.php#memory

Maybe you can poll /proc/$PID/status

Vincent

Why not simply use heaptrc ? It prints a summary ?

Michael.

Michael Van Canneyt wrote

> Maybe you can poll /proc/$PID/status Why not simply use heaptrc ? It prints a summary ?

Or even GetFPCHeapStatus.MaxHeapUsed.

Micha

Joost van der Sluis wrote

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

To me it seems that benchmark tests on 100000 records are missing relevance more and more.
I'm interested in responsiveness in n-tier solutions: opening connection
- begin transaction - quering/updating mostly 1 or a few records - commit transaction - close connection - browsing a small (<50) set of records . Opening /closing connections could be skipped in this context when a connectionpool is used.
Also I'm interested in tests selecting/updating/browsing sets larger than 1 million records, obviously local.
Consequently one could ask if one type of dataset could satisfy requirements regarding performance and use of resources in both cases.

Offcourse, but it has some usefull results.

I did those tests quickly. There wasn't any difference between the two. But compared to time the open/close takes, you can't measure the browse speed if you use only 50 records. So to test the browse speed, I simply used more records. (Also not fool-proof, but that wasn't my intention)

Also I'm interested in tests selecting/updating/browsing sets larger than 1 million records, obviously local.

That's simple, adapt the amount of records that is created, and then call the edit-field tests.

Consequently one could ask if one type of dataset could satisfy requirements regarding performance and use of resources in both cases.

I think you can. Unless you want to edit all 1 million records. (as I told in my last message)

It becomes different if you only need to browse one way through the records. (or, obviously, you have a dataset with only one record as result)

Or can you explain to me how you can make a dataset faster for usage with more records, and at the same time slow it down for low recordcounts? (or the other way around)

Regards,
Joost.

Op Tue, 17 Jul 2007, schreef Coco Pascal:

While relevant for many software, connect and disconnet are largely uninterresting, as opening a connection per query scales very bad. Properly designed software uses connection pooling; representative benchmarks should assume well designed software.

Daniël

I already stated that, or am I missing your point?

So this makes your case, doesn't it. Martin argued that his dataset was designed to work together with his visual controls. Because it makes no sense to use large datasets with those controls, one could argue that performance can't be relevant in this case.

I'm refering to very large buffered datasets held in memory by middleware for fast access. It is well known for instance that Delphi's TClientDataset chokes with say 100000 records, whereas different designs scale much better, TkbmMemTable for instance. Not long ago Marco van der Voort suggested (in another forum) to have a look at something like his "lightcontainers", apparently someting completely different (than for instance a TList based dataset).
I've put forward this point because for datasets used with visual controls speeds can't be the for very large sets it certainly is.

Yes, but it's an important part, because exactly this is the part that has changed. Except from this and the widestring-difference, there are no real differences. So I'm very curious know why his dataset works better with his visual controls.
I want to know this, so we can remove the need for two times the same component. Or that's at least what I hope for.

Because it makes no sense to use large datasets with those controls, one could argue that performance can't be relevant in this case.

Oh, that could be really true. But if there are no performance, and no other difference. Why would you allow the code-duplication?

Well, that's exactly the difference between mse's bufdataset and the fcl-db one. Tmsebufdataset is based on a TList, TBufDataset isn't. And I believe that at TList based dataset is always slower. (although it could save some memory)

But I need to do some new tests, to take the widestrings into account. And I have to implement a utf8<->ansistring conversion in TBufDataset. (Or somewhere else, I think that this conversion isn't the work of TBufDataset. Also because some db-engines has conversion-commands build into it. This has to be done on the TConnection-level.)

Regards,
Joost.

What I need for the MSEgui components is
- fast RECNO access for the DB grid proportional scrollbar mode (I think we talked about that when you changed TBufDataset to an linked list).
- internal indexes to switch sorting order in DB grids fast.
- calculated fields (which didn't work in TBufDataset at the time I needed them).
- internal calc fields (which are not implemented in TBufDataset up to now AFAIK).
- full BLOB and MEMO support (they where not fully functional in TSQLQuery at the time I needed them).
-"TClientDataset" functionality (not implemented in TBufDataset).
- the storage of strings as widestrings to prevent converting from/to unicode at every data access.
- the storage of strings as pointer to a variable length string to allow the MSEgui users to use long varchar fields without memory waste (TSQLQuery stored the popular PostgreSQL TEXT field with dsMaxStringSize = 8192 bytes).
- the limiting of the edited string length by character count and not byte count (think on multibyte encodings or current Linux installations with utf-8 locals).
- and possible more which comes into mind at the moment...

The architecture of tmsebufdataset are dynamic arrays of pointer which hold pointers to the recordbuffers.
One for the "physical" order and one for every internal index.

Martin

All this will not be a problem as this is why we'll merge the stuff in the first place: To have 1 set of components with the most possibilities. With the possible exception of the storage of strings as pointers. This is an internal issue which you should not worry about. The TEXT field in postgressql should be mapped to a blob field then.

Michael.