Performance breakdown with MySQL 4.1?!?

gto · Post by **gto** » 21.05.2008, 17:12

dragos!

Please, make a test application so we may find this bug!

I didn't notice it, maybe I almost nothing use ZROQuery

thank you!

dragos · Post by **dragos** » 22.05.2008, 07:50

OK, I prepared the test application (and simplified it in the process).
Compressed it's 29KB w/o libmysql41.dll and 483KB w/ libmysql41.dll.
It's a C++ Builder 6 application.

Now what do I do with it?

Post by **mdaems** » 22.05.2008, 10:12

Just file a bug report in the tracker (http://zeosbugs.firmos.at/) and attach the zip with dll. (unless it's exactly the same as the one we used to distribute)
As it's a C++ Builder app : also provide the exe so we can see what the result is when we have to rebuild it using Delphi. (I don't have C++Builder)

Mark

dragos · Post by **dragos** » 22.05.2008, 10:13

Here is the attached test project (C++ Builder 6).
It has a Readme.txt describing how to build it.

Someone please test it and confirm the issue.
Or just use the database and replicate the application in Delphi (it is very simple).

OT:
I'm using another user because I've just been locked out of the original one when I changed the email address in the profile and mistyped it.
I emailed the administrator ( zeoslib-forum@gmx.net ) describing the issue but I'm not holding my breath. If someone can help, please say so.

Post by **mdaems** » 23.05.2008, 00:27

It seems to be not only for mysql 4.1. Using 5.0 I have similar results.
As I didn't succeed compiling the sample in C++ I made a new Delphi project, copying everything from te original code to Delphi. I can confirm the difference is 1 to 10 at least in execution time.

I filed a bug report (http://zeosbugs.firmos.at/view.php?id=112). The attached zip contains the C++ and Delphi7 Projects.

Mark

Post by **mdaems** » 26.05.2008, 10:27

Hi,

I've been looking deeper into this bug. My first conclusion (but I should check this later) :
The difference is the type of resultset that is being sorted.
In case of a TZQuery this is a cached resultset (in Zeoslib memory buffers). In case of a TZReadOnlyQuery data is always retrieved from the database driver (libmysql) for every row to be compared. I suppose it's the overhead of library calls that slows this operation so much.
Have a look at the different implementation of TZAbstractCachedResultSet.CompareRows and TZAbstractResultSet.CompareRows. The last one uses MoveAbsolute() which maps to FPlainDriver.FetchRow for Mysql.

So, I don't think this issue can be resolved easily. You just have to keep in mind that a TZReadOnlyQuery doesn't cache it's data in the 'zeoslib buffers' and can only access data using library calls. For 'one off' situations as looping once through a dataset this isn't problematic. (You have to read the rows to be able to cache them internally anyway) But don't access rows/fields more than once if you can avoid it. (And sorting a dataset is the worst you can have, of course)

Mark

BTW : definitive conclusions follow later after some more testing.

dragos · Post by **dragos** » 26.05.2008, 11:57

So if I understand it correctly the issue seems to be related to caching (TZQuery caches data, TZReadOnlyQuery doesn't and on sorting there is a performance penalty when calling the mysql library).

If the problem isn't fixable (maybe that's the way it is supposed to work) then we could at least mention it in the FAQ (something like 'If you intend to sort the data on the clientside please use TZQuery instead of TZReadOnlyQuery for large datasets' or just recommend the usage of TZQuery for all situations) or add it to 'Known Bugs'.

Apparently there's no FAQ just Known Bugs/Installation.

Btw, could someone add to the Installation section the info I wrote in this post http://zeos.firmos.at/viewtopic.php?p=3 ... light=#311.

Would it be useful to have a FAQ distributed with the source code?
There are lots of useful tidbits on information about Zeos on the forum and its workarounds (like for example how to handle UTF since Zeos doesn't have UTF support).

Post by **mdaems** » 26.05.2008, 12:50

You do understand it correctly the way I wanted to explain. (I still must check if I was right, however). If this interpretation is right, it is not a bug, but a feature (with limited use, maybe).

Concerning the documentation files in the distribution : I need somebody to explain me how to easily edit the xml files used to generate these. It's some docbook format, but when I have to edit them using notepad, I refuse to do so.

So if somebody can show me a good (free) tool to edit these he's welcome.
Conclusion : these articles are outdated!

We just restarted the Knowledge Base module in the forum. Maybe the article you linked to could be published there. Feel free to post it as a KB item, I'll be very happy to accept it and eventually add a new category. If you have trouble posting a KB item, please send me a pm.

Mark

dragos · Post by **dragos** » 26.05.2008, 17:11

mdaems wrote: Concerning the documentation files in the distribution : I need somebody to explain me how to easily edit the xml files used to generate these. It's some docbook format, but when I have to edit them using notepad, I refuse to do so.
So if somebody can show me a good (free) tool to edit these he's welcome.

I use docbook for my own projects.
And I use vim (vi improved) to edit the xml files.
However, if you've never used vim/vi you'll definitely NOT like it.
It's a very capable general purpose editor and I recommend it (I use for almost anything).
Use gvim under Windows. It is free (open source).

Docbook is pretty simple for simple tasks - see http://en.wikipedia.org/wiki/Docbook for more info. There are also some links to editing software, but for my projects vim is enough.

mdaems wrote: We just restarted the Knowledge Base module in the forum. Maybe the article you linked to could be published there. Feel free to post it as a KB item, I'll be very happy to accept it and eventually add a new category. If you have trouble posting a KB item, please send me a pm.

I posted a C++ Builder related KB article.