Dataset sorting performance

[MJFShark]

Hi All!

I'm using a TZQuery that contains a single column recordset of 10,000 bigints (just the numbers 1 to 10,000). Using

ZQuery.IndexFieldNames := 'MYIDFIELD ASC';

Seems to take longer than I'd expect (about 4 seconds on my machine.) Tests of other sorting also seems to be slow where I don't expect it to be (ones not involving a fetch like CLOBs.)

Any thoughts or ideas on performance improvements related to this? I'm tracing through the code and I don't see anything obvious that I'm missing. Are there any properties that affect sorting performance? Thanks!

Update: This seems to only be an issue with MySQL (tested on 5.7 and 8.0). A very similar ZQuery on Oracle, PostgreSQL, and Firebird databases sort practically instantaneously. I'll do more testing, but does anyone have any ideas about what I'm seeing?

-Mark

[aehimself]

Mark,

At home, I have MySQL 8.0.18, client version: 8.0.18, database access component version: 8.0.0-015f7868e
Doing a simple select on an ID field returns >80k records.
Sorting is instant on my end.

Are you using 32 or 64 bit? What is the .dll version you are using?

[MJFShark]

I've tested with:

Both 32bit and 64bit with similar results and using both of these clients:
libmysql.dll: ver 6.1.11 (I can't use this one against my MySQL 8 server, so this one is just with 5.7)
mariadb.dll: ver 10.5.5 (Server versions 5.7 and 8.0)

My sampling profiler shows that 98% of the execution time (specifically during the sort) is in the client dll. It's possible I've done something to my source to cause this so I'm going to do some testing with a clean repository. Appreciate the confirmation, thanks.

-Mark

[MJFShark]

I've tested on a clean repos of 8.0.0-015f7868 with the same results. A bit stumped but I'll keep looking.

[aehimself]

Try the 64 bit up-to-date libmysql.dll.

Also keep in mind that clearing the sort calls refresh internally, that can slow things down. Setting sorting to asc or desc is not impacted by this.

[MJFShark]

I'll have to see if I can find that dll. I don't see it as a separate download from MySQL, it must be part of the bigger install set. I just tested a version of my exe from a few months ago and that one sorts instantaneously, so I suspect that it's a recent (within the last 3 months or so) change of some kind. Research ongoing lol. Thanks!

-Mark

[miab3]

Zeos8 svn7232 Delphi 10.3.3
For MySQL: server MariaDB 10.2-Win64, client MariaDB Connector/C 3.1.8-Win32
and 200000 records and a non-indexed field 'tbigint' (BIGINT random), sort:

on the client side:

procedure TForm2.Button4Click(Sender: TObject);
begin
ZQuery1.SortedFields:='tbigint';
end;

procedure TForm2.Button5Click(Sender: TObject);
begin
ZQuery1.SortedFields:='tbigint desc';
end;

procedure TForm2.Button8Click(Sender: TObject);
begin
ZQuery1.IndexFieldnames:='tbigint Asc';
end;

procedure TForm2.Button9Click(Sender: TObject);
begin
ZQuery1.IndexFieldnames:='tbigint Desc';
end;

and server side:

procedure TForm2.Button6Click(Sender: TObject);
begin
ZQuery1.SQL.Text:='select * from adqa_test order by tbigint';
ZQuery1.Open;
end;

procedure TForm2.Button7Click(Sender: TObject);
begin
ZQuery1.SQL.Text:='select * from adqa_test order by tbigint desc';
ZQuery1.Open;
end;

in less than 1 second