Performance issue using ADO.

[MJFShark]

I'm using the ADO protocol with a Firebird ODBC driver and having a pretty severe performance issue that seems to be related to retrieving field properties. The following test application takes roughly 6 seconds to run ZQuery.Open;. The same query using Delphi's DbGo TADOQuery object takes .3 seconds to open (same client machine and driver, etc.) I'd love to hear any thoughts on this. Perhaps I'm just missing a setting that reduces the field properties retrieved or some such...

Code: Select all

program ZeosAdoTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes, System.Diagnostics,
  Data.DB,
  ZDbcIntfs, ZConnection, ZDataset;

var
  ZConn: TZConnection;
  ZQuery: TZQuery;
  Sw: TStopWatch;
begin
  try
    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'ADO';
    ZConn.User := 'SYSDBA';
    ZConn.Password := 'somepassword';
    ZConn.Database := 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=MyDataSource';
    ZConn.Connect;

    ZQuery := TZQuery.Create(nil);
    ZQuery.Connection := ZConn;
    ZQuery.SQL.Text := 'select * from customers';
    Sw := TStopwatch.StartNew;
    ZQuery.Open;
    Sw.Stop;
    Writeln('');
    Writeln('Query opened in ' + Sw.ElapsedMilliseconds.ToString + ' ms');
    Writeln('Done');

    ZConn.Disconnect;
    ZQuery.Free;
    ZConn.Free;

  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Writeln('');
  Write('Press Enter to quit...');
  ReadLn;

end.

Stepping through the Zeoslib code shows that the performance hit is on:
in method: TZAdoResultSet.Open
...
for j := 0 to F.Properties.Count -1 do begin

It seems that the field properties get retrieved the first time they are touched in the loop and that takes about half a second or so. Just in case that helps.

Thanks for any help or insight!

-Mark

[MJFShark]

Here's the create table statement for the test:

Code: Select all

CREATE TABLE CUSTOMERS (
  CUSTOMERID D_IDKEY NOT NULL,
  CUSTOMERNAME VARCHAR(100),
  COMPANYNAME VARCHAR(100),
  BILLINGADDRESS VARCHAR(500),
  CITY VARCHAR(100),
  STATEORPROVINCE VARCHAR(100),
  POSTALCODE VARCHAR(20),
  COUNTRY VARCHAR(100),
  CONTACTTITLE VARCHAR(100),
  PHONENUMBER VARCHAR(100),
  FAXNUMBER VARCHAR(100),
  EMAIL VARCHAR(100),
  INVOICEEMAIL VARCHAR(100),
  MAILINGLIST D_BOOLEAN,
  RESELLER D_BOOLEAN,
  CUMULATIVEINVOICE D_BOOLEAN,
  PREFERREDPAYMENTMETHODID D_IDKEY,
  CREATEDTIMESTAMP TIMESTAMP DEFAULT 'NOW',
  NOTES VARCHAR(500),
CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMERID));

[marsupilami]

Hello Mark,

Egonhugeist says that this has to do with querying metadata. If you disable metadata fetching, things should speed up. Also this performance penalty shouldn't be as big if you use the same query repeatedly.

Best regards,

Jan

[MJFShark]

Hi Jan,

Thanks. I've tried setting ZConn.UseMetadata := False; and it doesn't seem to affect this at all. Is this what you mean by turning off metadata? It was the only setting that seemed to match. Opening and closing the same query multiple times seems to show no gain btw.

-Mark

[MJFShark]

I've also just tested the same code using the odbc_w protocol (as suggested by Jan in another post) and that one is blazingly fast. I get 0.1 seconds for opening the query compared to 6 seconds using the ADO driver.

-Mark

[EgonHugeist]

Hello Mark,

the ADO bridge is a "simple" user front end made by MS. I also noticed those performance penalties using that protocol.
Some more infos:
Since SQLServer 2005 the protocol talks UCS2/UTF16 only so using FreeTDS with Unicode-Compilers has the side effect that FreeTDS makes W2A conversion for the SQL's and the Fields (and we convert the Data again from A2W) on fetching the Data and vice versa on sending. This little overhad in combination the DBLIBAPI isn't able to retrieve exact type invormations (That's why Jan implemented a precaching + Metadata loading) makes FreeTDS slow. For the Ansi-Compiler it's worth it to use it (even if still not fast) but it is not fast for Delphi-Unicode.

That was the reason why i added OleDB first. I added the ODBC driver as a (none) Windows alterantive brotocol. In practice i noticed OleDB is loads faster than ODBC_A/W but ODBC is twice faster than FreeTDS and imbelievable faster than ADO.

Back to topic. As you mentionend TADOQuery takes 3 seconds. Arrm i can't say why our ADO is twice slower (yet), it's propably about some settings how it executes the queries. Hower 3 sec's are almost dead slow. The first bottlenecks:

• 1.ADO by it's self(we don't know what MS is doing in background)
  2.ADO uses OleVariants(permanental copy of data into the tagVariants including the use of the slow BSTR/WideStrings)
  3.SAFECALL convention thats like haveing a car with 600hp but the tires do allow 60mph only.
  4.It's a user frontend bridge and it doesn't makes sense to combine it with TDataSet's

. TDataSet's even if well known and very good documented do breaking down the performance by ~25% and that's by the overdoozed design inbetween. The faster the server the more the performance loss using TDataSet's. As an alternative API you can use ADO bridge directy whithout TDataSet or use the lightning fast ZDBC-Layer of Zeos directly.

ADO doesn't seem to be maintained anymore (not ADO for C# that's different) since almost 20 years. My oppinion: If the OleDB protocol whould support most providers ADO is able to support, i would vote for removing the Ado units and the protocol name. It's just code we need to maintain but as you pointed out: it's not worth it to use.

[MJFShark]

Thanks for that! I agree on the ADO replacement idea. It definitely seems redundant (more on this in my OleDB specific post.)

-Mark

[marsupilami]

3.SAFECALL convention thats like haveing a car with 600hp but the tires do allow 60mph only.

Sorry, but I doubt that part. I don't see why it should slow down things during regular operation. It is just stdcall used in a different way:
https://en.wikipedia.org/wiki/X86_calling_conventions#safecall.
Things just are a bit more comfortable when it comes to exceptions that are generated outside of ones own code, which definitly can be a life saver when developing on a small scale.

The rest is true though.

[EgonHugeist]

@Jan

the SAFCALL convention it's selve isn't the problem in first order.
The point is the compiler always adds the error testing around each call even if it's not required in some circumstances.
f.e. if we get objects from a Ole-Collection:

Code: Select all

  For I := 0 to AdoStatement.(OleCheck)Params.Count(OleCheck) -1
    do AdoStatement.(OleCheck)Params.(OleCheck)GetItem(I).DoSomthing

It's guarantieed we can't over- or underun the GetItem call, but we can't turn of the OleChecks while accessing the items.

However, the real bottleneck happens on higher places:

Code: Select all

  
  try
    RestartTimer;
    FAdoRecordSet := FAdoCommand.Execute(FRC, EmptyParam, adExecuteNoRecords);
    if BindList.HasOutOrInOutOrResultParam then
      LastResultSet := CreateResultSet;
    LastUpdateCount := FRC;
    if DriverManager.HasLoggingListener then
      DriverManager.LogMessage(lcExecPrepStmt, Self);
  except
    on E: Exception do
      FAdoConnection.HandleErrorOrWarning(lcExecPrepStmt, E, Self, SQL);
  end;

To have a consistent framework we need to catch the Exceptions and convert it to a EZSQLExcetpion/EDatabaseError.
each try ... do something ... Except ... end; kills the performance. The tires i'm talking about.

[marsupilami]

the SAFCALL convention it's selve isn't the problem in first order.
The point is the compiler always adds the error testing around each call even if it's not required in some circumstances.
f.e. if we get objects from a Ole-Collection:

Code: Select all

  For I := 0 to AdoStatement.(OleCheck)Params.Count(OleCheck) -1
    do AdoStatement.(OleCheck)Params.(OleCheck)GetItem(I).DoSomthing

It's guarantieed we can't over- or underun the GetItem call, but we can't turn of the OleChecks while accessing the items.

And what do you do in that case if a Param cannot be returned? Maybe because of an out of memory error or maybe because there simply is an error in the programming of the OleDB driver or an programming error in ADO itself that only gets triggered under rare circumstances? If GetItem has a way of telling us that there is a problem, we have to be prepared that it will do so - whatever we think how safe circumstances are.

However, the real bottleneck happens on higher places:

Code: Select all

  
  try
    RestartTimer;
    FAdoRecordSet := FAdoCommand.Execute(FRC, EmptyParam, adExecuteNoRecords);
    if BindList.HasOutOrInOutOrResultParam then
      LastResultSet := CreateResultSet;
    LastUpdateCount := FRC;
    if DriverManager.HasLoggingListener then
      DriverManager.LogMessage(lcExecPrepStmt, Self);
  except
    on E: Exception do
      FAdoConnection.HandleErrorOrWarning(lcExecPrepStmt, E, Self, SQL);
  end;

To have a consistent framework we need to catch the Exceptions and convert it to a EZSQLExcetpion/EDatabaseError.
each try ... do something ... Except ... end; kills the performance. The tires i'm talking about.

I thought that we agreed that the ADO driver can raise EZSQLExceptions/EDatabaseError and EOleExceptions as well as long as we document it? Why do we do that kind of rewrapping? We really do need to document design decisions.
Anyway - I am not going to introduce that kind of exception rewrapping for the proxy driver, because it simply is useless. The proxy driver is liable to return a wide spread range of exception types simply because of the technologies it uses besides Zeos internals: SOAP and TXmlDocument definitly have their own exception types. Even more so if the SOAP stuff gets pushed inside a DLL. I wouldn't be surprised, if TXmlDocument had different exception types depending on the provider that gets used (MS XML vs. ADOM vs. Omni XML vs. Apache vs. ...).
So - while it is a nice idea to have one exception that gets used for all kinds of database errors, for some divers it simply isn't feasible. Also normal Zeos drivers will throw exceptions that are not EZSQLException / EDatabaseError. The best example being Access Violations, that we still produce from time to time in some rare cases or exceptions that are generated by packages that we use only.

[EgonHugeist]

I thought that we agreed that the ADO driver can raise EZSQLExceptions/EDatabaseError and EOleExceptions as well as long as we document it? Why do we do that kind of rewrapping? We really do need to document design decisions.

Well we can document everything ... does it make sence?? Is it handy for the users? I don't think so. The more look to point 2 below.

Some more examples:
1. the compiler silently adds some lines around the method calls. But there is a trivial thing missing:

Code: Select all

if HResult <> S_OK (zero) then call @CheckAutoResult

means each call steps though houndreds of asm lines in CheckAutoResult.

2. We have a LoggingMechanism i.e. IZLoggingListener. How do you think we can log errors without catching the Exception???
The loggs a a general design and have nothing todo which API is used in background. OleException or others it doesn't matter. Of cause you can also document the proxy can't log errors... just my two cents, Jan.

[marsupilami]

I thought that we agreed that the ADO driver can raise EZSQLExceptions/EDatabaseError and EOleExceptions as well as long as we document it? Why do we do that kind of rewrapping? We really do need to document design decisions.

Well we can document everything ... does it make sence?? Is it handy for the users? I don't think so. The more look to point 2 below.

It makes sense. We do what we can do without shooting ourselves in the head - or in our foot. Exceptions can be caught by the user so he can log them if he wants to. I am pretty sure, they even can be simply logged to the same logger. In the world of ADO logging errors automatically simply doesn't make sense. One could also say it is a technical limitation.
Also there always are Exceptions that we don't log. Again: What do we do about exceptions from other units? Our own core Exceptions are not of the EZSqlThrowable type. The same goes for other error conditions. Examples are EOutOfMemory, EAccessViolation, EZeroDivide. These are harsh conditions but there might be more subtle ones. So - which Exceptions are we going to log then? Because if we want to log all Errors, we also have to log these Exceptions?

Some more examples:
1. the compiler silently adds some lines around the method calls. But there is a trivial thing missing:

Code: Select all

if HResult <> S_OK (zero) then call @CheckAutoResult

means each call steps though houndreds of asm lines in CheckAutoResult.

Erm - I disagree. As long as there is no Exception to be transported HResult will be S_OK and so @CheckAutoResult doesn't get called. So in a usual use case, there is a maximum of one more integer comparision combined with a jump. For me this hardly is a performance penalty. I seem to remember that the X86 architecture has a combined instruction for that kind of code (JNZ / JZ).

2. We have a LoggingMechanism i.e. IZLoggingListener. How do you think we can log errors without catching the Exception???
The loggs a a general design and have nothing todo which API is used in background. OleException or others it doesn't matter. Of cause you can also document the proxy can't log errors... just my two cents, Jan.

Well - the logger doesn't get used on a regular basis. Again - if there are Exceptions to log, the user can do it himself. The logger is interesting as a tool for debugging, checking what Zeos sends to the database. Not so much as an error log.

If we really really really needed this, we could have something like this:

Code: Select all

type
  TExecuteSQLFunc = procedure (SQL: String): IZResultSet;
  
  TSomeStatement = class(...)
    FExecSQL: TExecuteSQLFunc;
    function InternalExecuteSql(SQL: String): IZResultSet;
    function InternalExecuteSqlAndLog(SQL: String): IZResultSet;
    function ExecuteSQL(SQL: String): IZResultSet;
    procedure EnableLogging(Enable: Boolean);
  end;

function TSomeStatement.InternalExecuteSql(SQL: String): IZResultSet;
begin
  // do the execution of the SQL here...
end;

function TSomeStatement.InternalExecuteSqlAndLog(SQL: String): IZResultSet;
begin
   try
     Result := InternalExecuteSql(SQL): IZResultSet;
   except
     on E: Exception do begin
       LogMessage(E.Message);
       raise;
     end;
   end;
end;

function TSomeStatement.ExecuteSQL(SQL: String): IZResultSet;
begin
  Result := FExecSQL(SQL);
end;

procedure TSomeStatement.EnableLogging(Enable: Boolean);
begin
  if Enable then
    FExecSQL := InternalExecuteSqlAndLog
  else
    FExecSQL := InternalExecuteSql;
end;

This would spare us the setup and tear down the Exception handling. It will cost us one more jump and possibly one or two _Addref / _DecRef cycles. But maybe not.

Given all this, currently I don't intend to give up an easy to use tool for safe programming. Everything else would make my life much much harder. I simply don't see the benefit for investing my time there. With the current design decisions I can be pretty sure that memory management and exception handling will not make programs fail silently or in weird ways because I chose some well tesed mechanisms there, that actually help me concentrate on the core of my problem. And being able to concentrate on the core of my problem keeps me productive and helps me sleep better at night.
So yes - for the proxy driver it will be a technical limitation that it cannot log errors and that it will raise EOleError type Exceptions if anything happens on the level of the server or SOAP. Anybody who dislikes these design decisions is welcome to spend time on adding to the driver. My primary goal currently is to get it to work. If I ever have the time for it, I can think about speeding it up later on.

[EgonHugeist]

Erm - I disagree. As long as there is no Exception to be transported HResult will be S_OK and so @CheckAutoResult doesn't get called. So in a usual use case, there is a maximum of one more integer comparision combined with a jump. For me this hardly is a performance penalty. I seem to remember that the X86 architecture has a combined instruction for that kind of code (JNZ / JZ).

Tell me one compiler who is doing that. I didn't found one compiler who has that optimization. See:

Some more examples:
1. the compiler silently adds some lines around the method calls. But there is a trivial thing missing:

Code: Select all

if HResult <> S_OK (zero) then call @CheckAutoResult

means each call steps though houndreds of asm lines in CheckAutoResult.

I'd said this optimization is missing! Thus the SAFECALL convention is nice for RAD or beginners but finally dead slow because of all the reasons i wrote already. You should document all the excpetions of the proxy-driver Jan. Anyway good job!

[marsupilami]

Michael, this is from Delphi 10.4s System.pas:

Code: Select all

function _CheckAutoResult(ResultCode: HResult): HResult;
begin
  if ResultCode < 0 then
  begin
    [...]
  end;
  Result := ResultCode;
end;

Delphi 7 looks the same but there they also have an assembler implementation. So yes there is an additional function call. But the first thing afterwards is to check wether ResultCode is 0. So I wonder how big that performance impact really is. Especially in scenarios where a lot of other code on both sides of the safecall is executed and executing the safecall isn't the primary thing to do... Maybe it is time to let this discussion rest. I think for a good decision some kind of performance test would be needed.