Performance breakdown with MySQL 4.1?!?

[Mac]

Hi!

The last week i changed from BDE/ODBC to Zeos. After some trouble i changed to ADO/ODBC. And now, because i understood what caused the trouble, I got back to Zeos (nicer solution )

My customer now complained about a performance lack. There could be
several reasons for this. Maybe its only a fortuity. On my developer and
the test system i can't reconstruct these problems. They talk about
4-5 sec more for some things like changing to the next (complex) dataset.
So first there should be the database checked.
But i am interested, if i could have made something wrong in using zeos?
How can i tune up the performance? Did anyone else could report similar problems?

the computers at my customer have 800Mhz CPUs, while i am using an AMD64 3000+. Could be an important reason, why i can't reconstruct the problems of my customer ^^

tanks for replies and help

[pol]

Hi!

Maybe your customer has simply more data? That's a quite normal difference between test and production systems... Have you tried to issue the same queries on those system using the mysql command line tool or MySQL Browser and noticed some performance differences? Maybe some more indexes would help. I don't think Zeos is the problem.

Regards,
Rüdiger

[Mac]

I am testing with their data.
I am thinking like you. But I have to check everything, because the breakdown came, when the new client Version, which only difference is the db interface, was installed.
So I wonder if i made any mistakes in the implementation of zeos and my program. But there are not much documentatet options.

Is there a performance difference between TZQuery and TZReadOnlyQuery?
Or what makes TZReadOnlyQuey different in comparison with TZQuery, when handling results or requests?

greetings Mac

[pol]

Hi Mac!

Which new client version? MySQL client? Or your program? Did you use different db connection components before? Oh yes, BDE/ODBC. That should be slower than Zeos (ODBC!). But if I am right, now you use on both systems Zeos components, so it can't be that. Is the database in both cases the same, remote or local?

Regards,
Rüdiger

[zippo]

I think it's not a Zeos problem, but to be sure, use the problematic query thru Zeos (it's your App) and thru ODBC - if the second performs faster, then it is a Zeos problem. But be sure to do the test correctly!

If you find that the performance comparision is adequate (Zeos is faster), then you should check this:
- Could be the indexes? Indexes are very important for performance.
- Is the my.cnf file correctly configured (if server uses much data, needs more RAM).
- Is the client's server powerful enough - compared with your testing PC?
- You are testing on the same PC (server & app) - can you break it to a network test?

Hope it helps.

[Mac]

Hi!

Thanks for your replies. The configuration of the MySQL Server was something i advised the systemadministrator to check at first, before i posted here, but he is just someone who's working there at his freetime
As you know for customers, the programmer whoms software gets last on the PCs is the bad guy, what ever happens...
Today I was there to check this. I did some optimizing for the tables and everything was fine. Another reason could be, that they do a mysqldump
several times a day. So it could be possible that some of "slow" requests, where done, when the backup is running.
I hadn't time to check every job on that server, but I had shown, its working and played the ball back to them ^^

Yes I used BDE/ODBC before and for some testing ADO/ODBC. Would be strange, when Zeos as "wrapper" library for direct Connection dll file is slower then the BDE stuff.
I only wanted to check, if there was a way, I could use zeos totally wrong. What ever it could be. Because the last days, I was the "bad guy"

The Client systems at my customer are much slower, than my testing Network (Server and Development AMD64 3000+ for Mobile, and Client 1,2Ghz Duron, while they have AMDs with 800Mhz with bad performance)
Another discussion point with my customer...

Yes more Indexes could help. Some redesign will be done later.
But for now everythings seems okay.

Thanks for your replies.

Something to changing an existing Client Software with BDE/ODBC to Zeos:
Great Job. I only had to exchange the components and some classes.
The behaviour of the Query Classes with results are the same with MySQL and PostgreSQL. I renember with BDE/ODBC it wasn't (can't test. PostgreSQL ODBC and BDE just don't work at this time ).

regars
Mac

[zippo]

I never had problems regarding speed with Zeos (altrogh exists faster components). Compared to BDE is like SS Enterprise and a skateboard..

Indexes are VERY, VERY important for speed - the results of a joined query can be tremendously affected - up to several times faster (I experienced up to 100 fater times), so I suggest you to set them ASAP. Be sure that the server has enough RAM and the my.cnf is correctly set up.

mysqldump affects speed, but I can't udnerstand why it's triggered several times per day. Isn't better a RAID + once a day?

The client PCs are quite slower than yours, this can also be a problem.

About bad guy, I give you a tip: We are ALWAYS the bad guys! That's because users fear us They depeds on us, we decide if they can work or not, if they work 8 or 15 hours per day.... Just put some bugs and watch them getting green... :):)

[Mac]

I know how important indexes are... But at the moment there should be enough. Maybe one or two more at some points could speed up a little bit, but I have to analyze the responsible queries at first. But as I said, that everything is okay now...
I am only the programmer. I don't know why they don't use alternative backup strategies. I told the systemadministrator about the consequences of mysqldump, when he told me, that its executed so often...

At my visit an "optimize table" command for all tables was enough. They couldn't show any performance problems after that. To easy for so much trouble.

bug building: I am often thinking about alternative error Messages "Application terminates, because of stupid user", "How often I have to tell you, that I can't do this in that way ...", "I am not able to read your mind, please use the keyboard and the mouse. Mindreading Interface will be implementet as soon it is invented" or something like that ^^

regards
Mac

[fduenas]

Hi to seedup you programming maybe this can help you a bit.

Try to use the libmysql41.dll or any other in the same folder as your exe, this will prevent the program to load another libmysql.dll library that can be in the PATH environment variable.

try to not use a lot of LOOKUP fields this could slowdown the process of loading.

when using a grid component and the resultset of you query is large, set the property fo tzquery.filtered to true, even if there is no filter. why this? beacuse the grid use RecordCount property to get the total of records and what zeos does when calling recordcount it will fetch all recrods form the server at that time (it wil slow the process of loading for the first time only). so setting filetered to true will fetch the record when needed.

And because the last tip, Try not to call recordCount every time, its better to asisgn to value to a variable, or even better execute a query 'select count(*) from <table> where <filter> <other clauses>' so you can get the record count faster.

read your db server documentation for how to speedup queries, using indexes and writing cirrectly the queries using the WHERE or JOIN clauses.

[Mac]

The library is already in the same directory.
I will play a bit with the filter property.
That with the recordcount is interesting.

thanks
Mac

[zippo]

Yes, I often use recordcount, but never had any problems.. Can you explain more detailed?

[dragos]

Is there a performance difference between TZQuery and TZReadOnlyQuery?
Or what makes TZReadOnlyQuey different in comparison with TZQuery, when handling results or requests?

Yes there is if you sort the data using the query!

I had an application using a TZReadOnlyQuery to populate a TDBLookupComboBox with more than 2000 sorted entries (using SortedFields).
And it was slow.

After some profiling it turns out most of the time was spent in some sorting routine.

Replacing TZReadOnlyQuery with TZQuery made the problem go away.
And the data is still sorted.

Note: this happened on a 6.5.1 version. I don't know if the latest version exhibits the same issue.

[mdaems]

You should test it but it seems unlikely this has been changed.

On the other hand this difference seems odd. Are you sure the properties (SQL, Sortedfields,...) set in the ReadOnlyQuery were exactly the same as in the normal Query component? As the sorting code in the normal query is inherited from the readonly query it should make no difference at all.
Of course, if you sort using the SQL statement instead of using sortedfields, that's even better. In that case local sorting should not be necessary.

If the problem still exists, could you provide a sample program (+ data creation script) that shows the issue and file a bug report in the tracker at zeosbugs.firmos.at ? If you really need to send big (or confidential) datasets, please contact me using pm.

Mark

[gto]

A very old thread, I think..

Before dragos post, the latest posting was from zippo, at 28.11.2005.

[dragos]

Yes the thread is old.
I was browsing the forum looking the things that happened to me and possible fixes( after seeing gto's thread with duplicate entries that fixed one of my oldest annoyances that I was too lazy to search/fix for myself - I'm mostly a C++ guy...).

I have modified a test application to recheck the issue and it is there, at least on my zeos version - 6.5.1.

The application has 3 tables (a master-detail relationship test) and 2 DBGrids.
One of the fields is represented by a TDBLookupComboBox whose ListSource can be fed by a TZQuery or a TZReadOnlyQuery (to choose at runtime).

The queries are identical (SQL, Filtered, SortedFields).
The data comes from the 3rd table (1 for master, 1 for detail) and has 10000 records.
Refreshing the data ( qry->Open() ) takes about 0,3 seconds for the normal query and ~23 seconds for the readonly one.
If I clear SortedFields both finish immediately (<0.1s).

I can provide the test application, but first I have to remove lots of my 'helper' code (and it is C++, not Delphi).
I will try to simplify the application and replicate the problem.

Ever since discovering the issue I have replaced all usage of TZReadOnlyQuery with TZQuery in my applications.