TZQuery's Open too slow

[rossi73]

Hi there,

I've an interesting question. I'm using Delphi7 with ZeosLib 6.5.1, and want to run a query form a MySQL 4.1 dabatase. I have the following query:

SELECT A.Field1, A.Field2, A.Field3, MAX(B.Field4) AS NewName
FROM Table1 A, Table2 B
WHERE BINARY UPPER(SUBSTRING(A.Field1,1,3)) = '123'
AND A.Fieldx = B.Fieldx
GROUP BY A.Field1

Table1 and Table2 are indexed for all necessary fields. (But it's not important for my problem.)

When I run this query from MySQL Control Center, I get some (31) rows and the query time is 0,04 sec. It's great.

But when I run the query from Delphi using the TZQuery component's Open method, the running time is about 1 second. I've no idea why and how I could decrease the query time. I've tried for other queries as well, but the shorter string I give in the query, the slower the query runs...

E.g.: if in the mentioned query I change:
(SUBSTRING(A.Field1,1,2)) = '12', the query time:
MySqlCC: 0,06 sec (401 rows), Delphi: about 2,5 seconds.

If I change again:
(SUBSTRING(A.Field1,1,1)) = '1', the query time:
MySqlCC: 0,35 sec (4965 rows!), Delphi: about 8 seconds!

Remember: the queries are exactly the same and it takes too long until the Open menthod executes. Any idea? Thanks.

[Stevie]

Hi,

are you sure MySQL Control Center reads the whole bunch of records???
I don't think so, it only reads the information, that there are x records returned by your sql-statement, but it does not load all the data!

Btw. I do not know MySQL well enough to be sure, but try following:

WHERE BINARY UPPER(A.Field1) like '123%'

Regards

[rossi73]

Hi,

well, as I checked... The MySQLCC runs the query (1), then results the query time and the number of records (2), then loads the data and display them (3) in the grid.
The Delphi does: Runs the query (1) (this is the Open method, isn't it?), then loads the data and display them (3) (I make it with a code running through the Query, until Eof and display all the data as I need).

So: right after the query runs, the MySQLCC knows how many records will return and how long it has taken to get the data: it displays the query time. In Delphi, right after the Open method runs, I will know how many records will return (RecordCount method). So, that's why I think that the Open method itself is very slow and I don't know why.

BTW, LIKE is slower than SUBSTRING (first I used LIKE in the program, then I've changed and the program is now faster, but the Open method's speed is annoying... - if in MySQLCC it takes only 0.35 sec, why does it take about 8.00 sec in Delphi???) (LIKE vs. SUBSTRING - it doesn't matter now in this problem...)

[fduenas]

If you use RecordCount, thet tha's why, when using recordcount TZQuery will browse all the data from the server to count the records. MySQLCC uses the api call NumRows to return the number of resulting rows, thats why is much faster.
if attach a tzquery to a TDBGrid or something similar then thats another issue. DBgrid uses RecordCount to get the total fo records.

Try to use this:
Set TZQuery.Filtered to true (even if there is no filter specified), connect it to a dbGrid and then open it. You will see a speed up.

Pissible Solutions:
a) Try to learn about mysql api functions, you can use function GetRowCount from TZMySQLxxPlainDriver (check it in ZPlainMySQlDriver.pas)

b) Ask developer team to use the NumRows mysql api call within the RecordCount function to speedup the process.

My suggestion is to add a 'GetRowCount' function to the base class of the TAbstractRecordSet and it's interface, so it can be implemented to each descendant like TZMySQlRecordSet, TZADORecordSet, etc.

Regards