Locate extremely slow on ZeosLib components

[magrossi]

Hello,

I just stopped by to publish a few results I obtained while comparing the ZeosLib components against regular BDE components. More specifically locating records by key with TTable versus TZTable. Both using the "Locate" function.
I was wondering if something can be done regarding the very poor performance of the Locate command of the TZTable.

I made this comparisons because I just recently converted a legacy Paradox system to MySQL without changing much of the code (compatibility and time constraints), almost a simple component replacement (from BDE to ZeosLib).

When all was ready I noticed that the system was several times slower than before. After analyzing the code I noticed that it used Locate all the time. It was then that I decided to run a comparison between TTable.Locate and TZTable.Locate. Here are the results, please feel free to ask me whatever you want about the environment in which this test was run:

[Results.zip file Locate-CaseInsensitive.bmp]
- Using Locate with [loCaseInsensitive] in both TTable and TZTable.

[Results.zip file Locate-NoOptions.bmp]
- Using Locate with no options [] in both TTable and TZTable.

*** As a base test comparison I used the ZQuery component with the optimal query for the same "Locate".
*** i.e: Locate("Field1", [Value1], []) to "SELECT * FROM TABLENAME WHERE Field1=Value1".

Test environment

Machine: Intel Pentium IV 1.6Ghz 512Mb RAM
MySQL: Server Version 5.1 running as a service
Paradox: BDE Version 5.2.2
Table: Its a table with 39,209 records in it. The same data and structure are replicated in Paradox and MySQL. The key fields are strings. I might be able to send you the databases upon request (company property..).

Code snippet:

ZQuery Test

Code: Select all

const
  SQL_TXT = 'SELECT * FROM %s WHERE OPERANDO="%s" AND CLAVE="%s"';
begin
  ZConnection1.Disconnect;
  ZConnection1.Database := ADatabase;
  ZConnection1.Connect;

  while ARepeticion > 0 do
  begin
    ZQuery1.Close;
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add(Format(SQL_TXT, [ATable, Edit4.Text, Edit5.Text]));
    ZQuery1.Open;
    ZQuery1.First;
    Dec(ARepeticion);
  end;
end;

TZable Test

Code: Select all

begin
  ZConnection1.Disconnect;
  ZConnection1.Database := ADatabase;
  ZConnection1.Connect;

  ZTable1.Close;
  ZTable1.TableName := ATable;
  ZTable1.Open;
  while ARepeticion > 0 do
  begin
    ZTable1.First;
    ZTable1.Locate('Field1;Field2', VarArrayOf([Edit4.Text, Edit5.Text]), [{loCaseInsensitive}]);
    Dec(ARepeticion);
  end;
end;

TTable Test

Code: Select all

begin
  Table1.Close;
  Table1.DatabaseName := ADatabase;
  Table1.TableName := ATable;
  Table1.Open;
  while ARepeticion > 0 do
  begin
    Table1.First;
    Table1.Locate('Operando;Clave', VarArrayOf([Edit4.Text, Edit5.Text]), [{loCaseInsensitive}]);
    Dec(ARepeticion);
  end;
end;

If anyone can give me an idea of how to improve this I'll gladly try and do it myself and post back for the community. But I'm in a short time constraint here and any help would be much appreciated.

[trupka]

Using Table components (no matter which - BDE, IBX, ZEOS, etc) in client-server are generally bad thing and should be avoided. There is no such thing like table acces in C/S - tables are always simulated with SQL like "select * from tablename" and greatest advantage of database servers is lost.

IMO, the best way to increase performance is to replace table components with query. If you want to use locate (I now - it's nice, convenient, simple to implement and users like it) you can use simple trick - put first letter in searching phrase in SQL "WHERE" clause, reopen query and then continue with locate. Locate will work on reduced subset of data and will be faster.

[mdaems]

I suppose it's the TZAbstractRODataset.InternalLocate function that may need optimization.

Do you have a code profiler available in Delphi? I used gprof with Lazarus before and sometimes you can find that way what calls are done frequently.

One of the things to look at for : check if internally a readonly or a cached resultset is created. Because we had some other thread in these forums where slowness was due to the fact that a readonly mysql resultset doesn't cache in zeoslib but uses the mysql API to page forward and backward in the resultset. (Check this by debugging TZMySQLStatement.CreateResultSet to see if a CachedResultset or a NativeResultset is returned)

Mark

[magrossi]

Hello Trupka,

I'm perfectly aware of the inneficiency of the Locate method compared to doing the Query myself. I would do it if I could. Unfortunately for this project re-writing too much code is out of the question.

Thanks anyway for your time.

[magrossi]

Hello Mark,

I did the testing you proposed and the returned ResultSet is in fact a CachedResultSet. I just ran some other tests and the actual worst case scenario for the ZeosLib Locate method is when the record you are looking for is the actual selected record...
Any more ideas to help me fix that?

Cheers for the hard work,

Thank you

[magrossi]

Hi all,

I decided to write an external Locate function that takes a TZAbstractDataset as input and all the other parameters of the original Locate function. My method is a simple binary search and thus for can only be used when the dataset is orderer by the fields you are looking for. In a simples test case scenario with the same database I used the first time it takes me from 0ms to 15ms to find one record, the same result as the BDE Locate method as compared to the current ZeosLib implementation of Locate that takes up to 110 ms to locate the same record.

My problem was worked around but not solved,

If anyone wants the function implementation let me know. But it is really just a simples binary search algo.

Thanks to everyone that gave their input on this!