MySQL Sorting performance - readonly

[MJFShark]

Hi All!

For some reason my other post seems to give an error when I go to it.

I found out the issue, my code sets ZQuery.Readonly := True;

After that sorting it using IndexFieldNames or SortedFields causes a huge performance issue, but seemingly only for MySQL datasets. I've verified this on a simple console application outside my app. If someone else could test using ReadOnly := True I would appreciate verification that I'm not insane (at least in this case.) Once I removed that setting in my code I was able to sort 100,000 records in less than a second. With that setting on it took more than 5 minutes (I killed it at that point.)

-Mark

[miab3]

For me, with readonly, sorting also slows down drastically.

Michał

[EgonHugeist]

That was was i was thinking about viewtopic.php?f=50&t=132042

Same propably whill happen to Posgres too if (not default) someone enables the string-resultset.

According your issue:

Zeos uses the TZMySQL_Store_ResultSet as native resultset by default.

The defaults for MySQL are string based resultsets. And if your resultset should not be updateble i.e. ReadOnly = false, zeos uses the data from libmysql/libmariadb to avoid additional memory usage (those lib's are precaching the results and we can access them after fetch is complete, libpq of postgres behaves equal btw.)

MySQL supports binary resultsets only for "real prepared" queries. MariaDB has an additional (yet not implemented) way of doing that: mariadb_stmt_execute_direct see: https://mariadb.com/kb/en/mariadb_stmt_execute_direct/. Than we would access binary coded values only in our bound buffers.

Mark can you test to set

Code: Select all

TZDataSet.Properties.Values[DSProps_MinExecCntBeforePrepare] := '0';

for this query please?
That will force zeos to immediatelly prepare the query and we'll get the binary results running. This should prevent the conversion cycles from strings to it's pascal representation. MySQL is just a old dinosaur enbetween, nice for JSON's and PHP fans but ... that's just my POV others may think different.

Second advice to cirumvent it whould be:
set

Code: Select all

TZDataSet.Properties.Values[DSProps_UseResult] := 'True';

That will enable the TZMySQL_Use_ResultSet class to be used. This kind of results are forwards only to read. Yet i have no clue how many roundtrips this will procedure on the server, needs to be profiled. Result is we have minior memory consume of the lib's. Zeos will automatically load all data into a CachedResultSet if you aquire an scrollable resultset type. I'm using this option by default for all metadata queries.

We could also circumvent this case by an option, load the data to an CachedResultSet but keeps it ReadOnly. This whould hide this performance penalties but requires more memory....

[EgonHugeist]

Any news on this, Mark?

[MJFShark]

Ok, I'm a smidge slow on testing this! I had abandoned the idea of using TZReadOnlyQuery or setting ReadOnly to true for TZQuery. However, recently while looking at ZMemTable, I revisited this (and found this post) and the issue still seems to exist. Setting DSProps_MinExecCntBeforePrepare to '0' seems to have no affect at all in my tests.

These are the results of a simple query with 10,000 records, 5 columns where name is a varchar 50 column and others are just integers and other char fields.

TZQuery
Query Open: 36ms
Query Fetchall: 4ms
Q RecCount: 10000
Sort by name: 38ms

TZQuery with ReadOnly := True;
Query Open: 32ms
Query Fetchall: 1ms
Q RecCount: 10000
Sort by name: 7,118ms

TZReadOnlyQuery
Query Open: 29ms
Query Fetchall: 1ms
RecCount: 10000
Sort by name: 6,986ms

The latter two tests both end up in function TZMySQL_Store_ResultSet.MoveAbsolute(Row: Integer): Boolean; and are brutally slow. I find this part of the code a bit hard to debug, but I'm happy to try or test anything.

-Mark

[marsupilami]

Hello Mark,

do you have a test application and test data that you can share?

Best regards,

Jan

[MJFShark]

Hi Jan,

I've been trying to post a console mode program that shows the issue and I keep getting rejected by the forum security checker. I've been able to do it in the past. They seem to have dialed up the security settings so high that just simple sql statements seem to trigger it. I also can't seem to attach a dpr file. It's rather frustrating lol. Is there a trick I'm missing? I'll try again tomorrow.

-Mark

[marsupilami]

Hello Mark,

I sent a PM to you with alternate upload information.

Best regards,

Jan

[MJFShark]

I've uploaded that test.

I had missed the suggestion to try TZDataSet.Properties.Values[DSProps_UseResult] := 'True'; and adding that fixes the issue for TZReadOnlyQuery and also for TZQuery with ReadOnly := True. With that change the sorts all take about 250ms in my test.

-Mark