Slow on Linux

[andrea.lai]

I have create an CLX application that run on linux and on windows with mysql 5.0.15.

This application run very very slow on Linux but on Window is very fast.

Someone can help me to increase the performance ?

Thanks

[pol]

Slow concerning what? On the same machine (dual boot)?
Has it something to do with Zeos? And if, when? Connecting, opening a query...

[andrea.lai]

I have 2 identical machine 1 with WinXP SP2 and 1 with Linux.
The database is MySQL 5.0.15 ( on windows and on Linux ).

For example I have create an CLX applicatin and I have compiled it with Delphi 7 and with CrossKylix.

The application read a text file with 1000 record and update a TZTable using the "Locate" to search the record and using "Append/Post" or "Edit/Post" .

On Windows update the table in 1 minute a 30 seconds and on Linux in 3 minutes.

The problem is identical if I use TZQuery or TZSQLProcessor.

If I must update 20000 record the time is very important and on Linux the time is double that on windows.

[pol]

Can you execute this update statemeint in the mysql command line tool (mysql) on both systems and see if it is slower on Linux too? Maybe making a script out of your text file.
Another thing: what part is slower: the locate or append or insert?
If time is important for you maybe it would be better to work with queries instead of a table, especially if the table gets bigger.

[andrea.lai]

I have just create update statment on external sql file and I have run the command "mysql < file.sql" and in the same system mysql work fast.

The problem is identical infact if I use this :

if ZANAGRAF.Locate('ANA_EAN',COD,[loCaseInsensitive]) then
begin
ZANAGRAF.Edit;
ZANAGRAF.fieldbyname('ANA_DSC').AsString := data;
ZANAGRAF.Post;
end
else
begin
ZANAGRAF.Append;
ZANAGRAF.fieldbyname('ANA_EAN').AsString := COD;
ZANAGRAF.fieldbyname('ANA_DSC').AsString := data;
ZANAGRAF.Post;
end;

to locate the record and decide to update or insert

The code with query is :

ZQry.SQL.Clear;
ZQry.SQL.Add('SELECT ANA_EAN FROM ANAGRAF WHERE ANA_EAN="'+COD+"');
ZQry.Open;

if ZQry.RecordCount > 0 then
begin
ZQry2.SQL.clear;
ZQry2.SQL.Add('UPDATE ANAGRAF SET ANA_DSC="'+data+'"');
ZQry2.ExecSQL;
end
else
begin
ZQry2.SQL.clear;
ZQry2.SQL.Add('INSERT INTO ANAGRAF (ANA_COD, ANA_DSC) VALUES("'+COD+'","'+data+'")');
ZQry2.ExecSQL;
end;

With 2 query is much slower than with table.

Tha part slower is ( for example ) when use the "while ... end;" cicle

while not(ZANAGRAF.Eof) do
begin

....

ZANAGRAF.Next;

end;

this ( for example ) is very slow ...

Another slower part is if I run much query contemporanement or if i use the

Edit;
...
Post;

[pol]

This surprises me. Is there an index on ANA_EAN? I wouldn't use RecordCount. You could use ZQry.EOF instead. And maybe (I'm not sure about MySQL) using bind variable can speed up queries. With Oracle that would help a lot.

[andrea.lai]

Yes there is an index on ANA_EAN...
Now I test using EOF.

How I set the bind variable ?

[pol]

Query: select * from ANAGRAF where ANA_EAN = :ANA_EAN
before Query.Open: Query.ParamByName('ANA_EAN').AsString:=COD

same for Insert and Update