Grave performance issue

[RH]

Hi all

I have a problem with TZReadOnlyQuery being too slow. I am loading app 50-100 K records, and it is taking upto 5-10 seconds. The exact same code has been used with Devarts MyDac and takes 0.2 seconds. Even if the connection method of MyDac is set through the LibMySQL.dll, it is the same way.

I use the latest stable Zeos 6.6
MySQL 5.4
The proper LibMySQL.dll
Delphi 7
Win XP

My code is as follows


//////////Creation and initialization part

FConnection:= TZConnection.Create(nil);
FDataSet:= TZReadOnlyQuery.Create(nil);

with FConnection do
begin
HostName := 'localhost';
PassWord := 'pass';
Port := 3306;
User := 'user1';
LoginPrompt := False;

Protocol := 'mysql-5';
AutoCommit := False;
SQLHourGlass := False;
TransactIsolationLevel:= tiNone;
Catalog :='';
Database :='';
ReadOnly := True;
DesignConnection := False;
end;

with FDataSet do
begin
Connection := FConnection;
ParamCheck := False;
AutoCalcFields := False;
Filtered := False;
SortType := stIgnored;
end;

FConnection.Connect;


//////////Opening and loading

FDataSet.Open('select * from DB.Table limit 30000');
while not DataSet.EOF do
begin
s:= s + DataSet.FieldByName('ID').AsString;
dbc.Next;
end;



If I load the ID field as Integer, things are faster (arround ½ second).
Is there anything I can do about this ?

[mdaems]

- If the table has more than 1 field you could replace the * by the field name. That reduces the amount of data to be transfered.
- You could avoid using FieldByName. DataSet.Fields[1].AsString should be faster.
- Try to set IsUniDirectional to true.
- Try to use a TZQuery. This seems odd advise, but sometimes it's just quicker.

I suppose the last paragraph should be

Code: Select all

FDataSet.Open('select * from DB.Table limit 30000');
while not FDataSet.EOF do
begin
s:= s + FDataSet.FieldByName('ID').AsString;
FDataset.Next;
end; 

Otherwise I'm afraid you are mixing things up.

Just a question: is the code above the real problem query or s it just an example? If it's an example : did you test the performance issue as well?

Mark

[RH]

Hi

Thank you for your response.

Regarding the code: That is actually the code I have used. I had called the TReadOnlyQuery "dbc" but changed the name to FDataset when I posted the code here, so it would be easier to understand. I apologize that I had forgot to change the name everywhere, and for causing confusion. But to answer your question: yes it is the real code, and yes I have tried to time it and the problem is there still

Further: I have made additional tests to see how the performance is dependant on the number of records retrieved (=number og runs inside the loop)

K-records seconds
100............23
80..............12
50..............5
30..............1,5
20..............0,6
10..............0,2
5................0.04

so 5.000 records take 0,04 seconds. One would expect 100.000 records to take 0.8 seconds. But 100 K records take 23 seconds. Why is this occuring ?

To solve the problem I have tried your suggestions and then some
1) tried with TQuery: things got ALOT worse
2) tried with Fileds[1], this actually cut execution time in half. But still no where near MyDacs superfast timings
3) IsUnidirectional is ReadOnly, so I cannot set it

And finally I installed the latest snapshot (as opposed to the stable version I used uptil now) and tried to set FetchRows. This did not help at all.

And the question still remains: Why does MyDac (from devart) run through the same code with 100 K records in 0.35 seconds while ZeosLib use 23 seconds ?

And does anyone else have same problem ? Can you get you records with higher speed ? Tha Table I am using is a simple table with 3 fields. The table is indexed, it is on MySQL and uses the MyIsam engine.

[klchin]

Hi,

Possible slowdown could be cause by the "String" operation,
s := s + FDataSet.FieldByName('ID').AsString;

How about the speed if you just assigning the Stirng,
s := FDataSet.FieldByName('ID').AsString;
I think this should give you more accurate speed test.

[RH]

Actually you are right. In this particular case using
s := FDataSet.FieldByName('ID').AsString;
does cut the time for loading 100 k records down to 0.7 secs

But still. This must be a ZeosLib problem and not a Delphi problem
How do I know: well using other components such as MyDac, does not generate the same problem. Is it something with internal handling of strings by Zeos components ?

Regarding the code: this is just a part of my code. I have a large application with a lot of string handling, and loading from database on startup.
This means, I cannot use the fix above (and similar fixes) in all cases in my application
The application loads in about 7 seconds using MyDacs components. But I have waited 5 minutes for it using the Zeos components, and it still had not finished the startup rutines. I am surprised no one has had such issues before. I thought there were some setup tweaks one could apply ? Something with calculated fields, paramcheck, filtered ? How do i set the thing to unidirectional ?

And by the way: this is another problem, I thought i let the development team know. If you use the snapshot TRUNK_REV693, and set the FetchRow property of the TReadOnlyQuery to say 100, then you load say 2000 records from a table. Now if you call the RecordCount property the result will be 100 (I had expected it to be 2000). Is this a bug or is it deliberate ?

[mdaems]

Hi,

In this particular case using
s := FDataSet.FieldByName('ID').AsString;
does cut the time for loading 100 k records down to 0.7 secs

I think this can be explained because of the optimizer optimizing away the assignment as the field isn't used afterwards... So you should put some statement that uses the s. (eg. print the first character)

Idea 1 makes things worse : this means TZReadOnlyQuery does not cache the data internally by accident (as intended).
Idea 2 : it's quicker anyway, and it might make MyDac even faster as well.
Idea 3 : Sorry for this one. The result of idea 1 proves it doesn't matter and I think it's true anyway (just for curiosity :did you check the readonly value at runtime?).

Concerning the Fetchrow 'bug', I think it was intended. Because RecordCount would force reading all records before the result was known. Does calling Last before recordcount give the right result?

Mark

[RH]

Hi

regarding the optimization concern: the speedup is still there if I run the following loop

FDataSet.Open('select * from DB.Table limit 30000');
while not FDataSet.EOF do
begin
s:= FDataSet.FieldByName('ID').AsString;
if s='something' then showmessage(s);
FDataset.Next;
end;

In the above loop the setting of s from the record is not optimized out. Still it is much faster than when the record was added to s (as in my first example)
But why is this problem only present for ZeosLib and not other components ?

Regarding readonly:
There is no ReadOnly property for TZReadOnlyQuery. However if I use TZQuery,and set ReadOnly to True, then the speed is the same as if i use TZReadOnlyQuery !!!
Remember when I said that TZQuery made it alot worse ? well that was only because i did nothing with the ReadOnly property. If I set it to True then it is neither worse nor better.

[mdaems]

Ohoh... that almost proves the dark compiler behaviour might be guilty after all. I'm not a very experienced Delphi programmer, so what comes is guess work.

It might be a mad question, but can it be possible that the string type used by MyDac is different from the Zeoslib implementation, that way invoking some background data conversions?

Another try : String memory issues? Like the memory assignment is quite 'fixed' when Mydac is used and always reassigned for the zeoslib case? This could be true when zeoslib assigns new buffers when reading a new value, blocking expansion of the growing string at every read.

Mark

[klchin]

Hi,

If you wanted to deal with alot of String operation,
why not try to use fixed or allocated char array
to concatenate the string.