UNIDac for ODBC. Query slower than ADO and BDE?

[jdredd]

Using Delphi 7 and Latest UNIDAC demo components ( version 5.3.9 )

Been using ODAC for a few years now with great results.

I have been using ADO for years now to interface with Intersystems Cache
database via ODBC.

Been looking to see if Unidac is faster, but so far as I can tell, its 2.5-3x
slower in cases.

My test is to have a simple query that selects from a table and fetch data.
In this case 2313 records, 71 columns.

I use a timestamp before and after work for each connection type.

They run one right after each other. No threads, as don't want them
fighting for speed. I even changed up the order of them being called.

Now the crazy thing is.. I tried BDE to access this, and it was quicker then Unidac.

In one test it would come out like this

2313 records to fetch after the query for them all. Which is correct for them all.

ADO took avg 13 seconds
UNIDac took avg 37 seconds
BDE took avg 26 seconds

All my tests querying data that is of decent size, Unidac always looses to
ADO and BDE.

If its a smaller dataset, say 200 records with 8 columns, they are all
pretty close in time... with in a tenth of second if that.

But once the data set gets bigger, the slowness starts to show up.

Any ideas?

[AlexP]

Hello,

There is a code below, that compares UniDAC and ADO performance on intersystems Cache database via ODBC. The test results on a table with 71 fields and 2500 records show that UniDAC is almost 3 times faster than ADO.

Code: Select all

program pConsole;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Windows,
  DB,
  Uni,
  DAScript,
  UniScript,
  ODBCUniProvider,
  ADODB,
  ActiveX;

var
  UniConnection: TUniConnection;
  ADOConnection: TADOConnection;
  i: integer;

procedure CreateTable;
var
  UniSQL: TUniSQL;
  script: string;
  i, j: integer;
  fields, values: string;
begin
  UniSQL := TUniSQL.Create(nil);
  try
    UniSQL.Connection := UniConnection;
    try
      UniConnection.ExecSQL('DROP TABLE SAMPLE.TEST');
    except
    end;

    script := 'CREATE TABLE SAMPLE.TEST (' + #13#10 +
              ' F_ID INTEGER,' + #13#10;
    for i := 1 to 70 do
      script := script + '  F_TEST_' + IntToStr(i) + ' VARCHAR(50),' + #13#10;
    script := script + 'CONSTRAINT TEST_PK PRIMARY KEY (F_ID))';

    UniSQL.SQL.Text := script;
    UniSQL.Execute;

    fields := 'INSERT INTO SAMPLE.TEST (F_ID, ';
    values := #13#10 + 'VALUES(:F_ID, ';
    for i := 1 to 70 do begin
      if i = 70 then begin
        fields := fields + 'F_TEST_'+ IntToStr(i) + ') ';
        values := values + ':F_TEST_'+ IntToStr(i) + ')';
      end
      else begin
        fields := fields + 'F_TEST_'+ IntToStr(i) + ', ';
        values := values + ':F_TEST_'+ IntToStr(i) + ', ';
      end;
    end;

    UniSQL.SQL.Text := fields + values;
    UniSQL.Prepare;

    Randomize;
    for i := 1 to 2500 do begin
      UniSQL.ParamByName('F_ID').AsInteger := i;
      for j := 1 to 70 do begin
        UniSQL.ParamByName('F_TEST_' + IntToStr(j)).AsString := 'test_' + IntToStr(Random(100)*j);
      end;
      UniSQL.Execute;
    end;
  finally
    UniSQL.Free;
  end;
end;

function testUniDAC: integer;
var
  UniTable: TUniTable;
  c: cardinal;
begin
  Result := -1;
  UniTable := TUniTable.Create(nil);
  try
    UniTable.Connection := UniConnection;

    UniTable.TableName := 'SAMPLE.TEST';

    c := GetTickCount;
    UniTable.Open;
    while not UniTable.Eof do begin
      for i := 0  to UniTable.FieldCount - 1 do
        UniTable.Fields[i].AsString;
      UniTable.Next;
    end;
    Result := GetTickCount - c;

  finally
    UniTable.Free;
  end;
end;

function testADO: integer;
var
  ADOTable: TADOTable;
  c: cardinal;
begin
  Result := -1;
  ADOTable := TADOTable.Create(nil);
  try
    ADOTable.Connection := ADOConnection;
    ADOTable.TableName := 'SAMPLE.TEST';

    c := GetTickCount;
    ADOTable.Open;
    while not ADOTable.Eof do begin
      for i := 0  to ADOTable.FieldCount - 1 do
        ADOTable.Fields[i].AsString;
      ADOTable.Next;
    end;
    Result := GetTickCount - c;
  finally
    ADOTable.Free;
  end;
end;

begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ConnectString := 'Provider Name=ODBC;Data Source=TRYCACHE;User ID=_system;Password=root';
    UniConnection.Connect;
    CreateTable;
    Writeln('Fetch 71 fields with 2500 records in UniDAC: ' + FloatToStr(testUniDAC/1000) + ' c.');
  finally
    UniConnection.Free;
  end;

  CoInitialize(nil);
  try
    ADOConnection := TADOConnection.Create(nil);
    try
      ADOConnection.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=_system;Data Source=TRYCACHE';
      ADOConnection.Connected := True;
      Writeln('Fetch 71 fields with 2500 records in ADO: ' + FloatToStr(testADO/1000) + ' c.');
    finally
      ADOConnection.Free;
    end;
  finally
    CoUninitialize;
    readln;
  end;
end.

[jdredd]

Here is what my code looks like...

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
var q:string;
begin
 q := 'select * from Inventory where part = "ENG"';
 adoquery1.sql.Text := q;
 query1.SQL.Text := q;
 uniquery1.SQL.Text := q;
 memo1.Clear;
 memo2.clear;
 memo3.clear;
end;

procedure TForm1.Button1Click(Sender: TObject);
var c1,c2,c3,c4,c5,c6: cardinal;
begin
 memo1.Clear;
 memo2.clear;
 memo3.clear;

////

 c1 := GetTickCount;
 memo1.lines.add('ADO');
 memo1.lines.add('Start Time = '+timetostr(now));
 adoquery1.DisableControls;
 adoquery1.Open;
 memo1.lines.add('Record Count = '+inttostr(adoquery1.RecordCount));
 adoquery1.Close;
 memo1.lines.add('End Time = '+timetostr(now));
 adoconnection1.Close;
 c2 := GetTickCount - c1;
 Memo1.lines.add('Seconds = '+floattostr(c2/1000));
////

 c3 := GetTickCount;
 MEMO2.lines.add('UNI');
 memo2.lines.add('Start Time = '+timetostr(now));
 uniquery1.DisableControls;
 uniquery1.open;
 memo2.lines.add('Record Count = '+inttostr(uniquery1.RecordCount));
 uniquery1.Close;
 memo2.lines.add('End Time = '+timetostr(now));
 uniconnection1.close;
 c4 := GetTickCount - c3;
 Memo2.lines.add('Seconds = '+floattostr(c4/1000));
 ////

 c5 := GetTickCount;
 MEMO3.lines.add('BDE');
 memo3.lines.add('Start Time = '+timetostr(now));
 Query1.DisableControls;
 query1.open;
 query1.FetchAll;
 memo3.lines.add('Record Count = '+inttostr(query1.RecordCount));
 query1.Close;
 memo3.lines.add('End Time = '+timetostr(now));
 QUERY1.close;
 c6 := GetTickCount - c5;
 Memo3.lines.add('Seconds = '+floattostr(c6/1000));

end;

my results for this case

Code: Select all

UNI
Start Time = 10:29:59 AM
Record Count = 960
End Time = 10:30:13 AM
Seconds = 13.509

Code: Select all

ADO
Start Time = 10:30:13 AM
Record Count = 960
End Time = 10:30:16 AM
Seconds = 3.12

Code: Select all

BDE
Start Time = 10:30:16 AM
Record Count = 960
End Time = 10:30:27 AM
Seconds = 11.186

One thing I tried this on 4 different servers in different locations and pretty much
same results.

AlexP - If you can email me at [email protected] , I can give you one of our QA server
settings to connect to and try out. It will be using Cache 2012 2.2


I have probably asked before.. but what would it take $$$$$$$$$ to get a native component
for Cache, and just dump ODBC?

[AlexP]

Please send me the connection string (UniDAC/ADO), table name, and specify the ODBC driver settings at creating DSN to alexp*devart*com.

[jdredd]

Please send me the connection string (UniDAC/ADO), table name, and specify the ODBC driver settings at creating DSN to alexp*devart*com.

It has been sent. Thanks.

[AlexP]

Thank you, I have received your sample. On your project UniDAC is 2 times faster o average. The test was conducted on the following driver version: InterSystems ODBC35 2014.01.01.702 from 27.05.2014

[jdredd]

Thank you, I have received your sample. On your project UniDAC is 2 times faster o average. The test was conducted on the following driver version: InterSystems ODBC35 2014.01.01.702 from 27.05.2014

Thanks.. sent you some email replys.

On my own dev machine and a few remote testers, they all are 2-3x slower.

I found a few machines to run this on in our datacenter, and it was 2-3x quicker.

But right now it seems very hit and miss on speed

Driver versions ranged 2012-2014 and OS in every instance was win7 64bit.

I am stumped. Machines where UNIDac looses, ADO is MUCH faster... and then BDE
beating out Unidac next.

I don't have any other ODBC to try out yet to see if same issues, but I can't think
of what the problem can be yet.

Very hit and miss on this one

[jdredd]

One of my tests ran in on to machines at his place.

ADO was 2-3x quicker on one.

2nd machine, UNIdac was a few tenths of a second quicker.

But on a repeat test, it flip flopped.. with ADO again being much
faster then the unidac test.

this is so odd.

[AlexP]

Even on multiple execution of the query, UniDAC surpasses ADO in speed on our PCs. Below is a code comparing average time of your query execution 10 times in a row. Please specify the ODBC driver version you are using.

Code: Select all

program pConsole;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Windows,
  DB,
  Uni,
  DAScript,
  UniScript,
  ODBCUniProvider,
  ADODB,
  ActiveX;

var
  i: integer;
  AdoCnt, UniDACCnt: Double;
  UniConnection: TUniConnection;
  ADOConnection: TADOConnection;

function testUniDAC: integer;
var
  UniQuery: TUniQuery;
  c: cardinal;
begin
  Result := -1;
  UniQuery := TUniQuery.Create(nil);
  try
    UniQuery.Connection := UniConnection;
    UniQuery.SQL.Text := 'select * from Inventory where part = "ENG"';
    c := GetTickCount;
    UniQuery.Open;
    UniQuery.Last;
    Result := GetTickCount - c;
  finally
    UniQuery.Free;
  end;
end;

function testADO: integer;
var
  ADOQuery: TADOQuery;
  c: cardinal;
begin
  Result := -1;
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := ADOConnection;
    ADOQuery.SQL.Text := 'select * from Inventory where part = "ENG"';

    c := GetTickCount;
    ADOQuery.Open;
    ADOQuery.Last;
    Result := GetTickCount - c;
  finally
    ADOQuery.Free;
  end;
end;

begin
  try
    AdoCnt := 0;
    CoInitialize(nil);
    try
      ADOConnection := TADOConnection.Create(nil);
      try
        ADOConnection.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=...;Data Source=';
        ADOConnection.Connected := True;
        for i := 1 to 10 do
          AdoCnt := AdoCnt + testADO;
        AdoCnt := AdoCnt/10000;
      finally
        ADOConnection.Free;
      end;
    finally
      CoUninitialize;
    end;

    UniDACCnt := 0;
    UniConnection := TUniConnection.Create(nil);
    try
      UniConnection.ConnectString := 'Provider Name=ODBC;Data Source=;User ID=...;Password=...';
      UniConnection.Connect;
      for i := 1 to 10 do
        UniDACCnt := UniDACCnt + testUniDAC;
      UniDACCnt := UniDACCnt/10000;
    finally
      UniConnection.Free;
    end;
  finally
    WriteLn('ADO = ' + FloatToStr(AdoCnt) + ' s; ' + 'UniDAC = ' + FloatToStr(UniDACCnt) + 's');
    readln;
  end;
end.

[jdredd]

Driver versions tested on 12 different machines around the country.

5.02.00.329
2012.01.02.702
2013.01.00.446
2014.01.00.608

All versions had mixed results on if Unidac or ADO was quicker, but for the
most part for most machines ADO was quicker by 2-3x no matter what
version.

I think Unidac is "ok" but that something else in windows is screwing with its
access. When BDE is faster in cases, something has to be up.

[AlexP]

Please specify the characteristics of the PC, the OS and the version of the ODBC driver, on which ADO is 2-3 times faster than UniDAC - and we will try to check performance on the same configuration.

[jdredd]

Please specify the characteristics of the PC, the OS and the version of the ODBC driver, on which ADO is 2-3 times faster than UniDAC - and we will try to check performance on the same configuration.

-----------------------------------
Machine 1
Windows 7 64 bit
6gb of mem
i5 CPU
Not much running on it other then dev tools.
Driver 2014.01.00.608
ADO beats Unidac every time by 2-3x speed.
-----------------------------------
Machine 2
Windows 7 64bit
Dual Core DUO
4gb of mem
Driver 5.02.00.329 Originally.
Upgraded to 2014.01.00.608, no change.
ADO beats Unidac every time by 2-3x speed.
-----------------------------------
Machine 3
Windows 7 64bit
8gb of mem
i5 CPU
Driver 2013.01.00.446
ADO beats Unidac every time by 2-3x speed.
-----------------------------------
Machine 4
Dual Dual-Core AMD Opteron 8214HE
Win2k8 R2 with sp1 64bit
16gb of mem
Driver 2014.01.00.608
This machine ADO and UNIDAC run neck and neck. Within a few
hundredths of a second of each other if not the exact same time.
-----------------------------------
Machine 5
XP sp3
Core2 DUO
1gb mem
Driver 2012.02.04.954
This machine ADO and UNIDAC run neck and neck. Within a few
hundredths of a second of each other if not the exact same time.
-----------------------------------
Machine 6
XP sp3
Core2 DUO
3gb mem
Driver 2012.02.04.954
ADO beats Unidac every time by 2-3x speed.
-----------------------------------

Nice range of systems and configs here I think. Only 1 of these machines
I know where it is phsyically.. and thats Machine 1.. my dev laptop. Rest
are either down in our data centers here or across town.. or across the country.

The server connecting to sits across the desk from me.

[AlexP]

We have checked performance again on

Windows 7 64 bit
8gb of mem
i5 CPU
Driver 2014.01.01.702

and

Windows XP 32 bit
4gb of mem
i3 CPU
Driver 2014.01.01.702

And the results show that UniDAC is 2 times faster in average than ADO.

[jdredd]

Got any ideas then? I can't go forward with using this as I can't get constant results
myself.

When my own dev machines, 100/100 tests always comes out with ADO coming out faster,
I can't push for this to be used as with the results I am seeing on testers machines
around the country, Unidac doesn't work out to be worth converting over.

Only things I can think of is either Trail version is the problem? or something
in Windows that messes with it?

I am unsure what I am doing wrong if anythin. I am doing nothing fancy. I haven't
tweaked any of the components to try to mess with them. And the fact BDE beats it
out in cases, tells me there has to be something "off" but so far I haven't
been able to tell whats up.

[AlexP]

No, these encryption features are not implemented in our product.