SynOleDB: OpenSource Unit for direct access to any database via OleDB

ab · 2011-06-27 14:41:19

That's it, our SynOleDB seems alive and running well.

OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing data from a variety of sources in a uniform manner. It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement SQL.

Just a small list of available providers from http://www.carlprothman.net/Default.aspx?tabid=87
- Active Directory Service
- Advantage
- AS/400 (from IBM)
- AS/400 and VSAM (from Microsoft)
- Commerce Server
- DB2
- DTS Packages
- Exchange
- Excel
- Internet Publishing
- Index Server
- Microsoft Jet
- Microsoft Project
- MySQL
- ODBC Databases
- OLAP Services
- Oracle (from Microsoft)
- Oracle (from Oracle)
- Pervasive
- Simple Provider
- SQLBase
- SQL Server
- SQL Server via SQLXMLOLEDB
- Sybase Adaptive Server Anywhere
- Sybase Adaptive Server Enterprise
- Text Files
- UniData and UniVerse
- Visual FoxPro

Here is some working sample program, using our SynOleDB unit to connect to a local Microsoft SQL Server 2008 R2 Express edition, which will write a file with the JSON representation of the Person.Address table of the sample database AdventureWorks2008R2:

program TestOleDB;
{$APPTYPE CONSOLE}
uses
  SysUtils,
  Classes,
  SynCommons,
  SynOleDB;
  
var Props: TOleDBConnectionProperties;
    Conn: TSQLDBConnection;
    Query: TSQLDBStatement;
    F: TFileStream;
begin
  with OleDBSynLogClass.Family do begin
    Level := LOG_VERBOSE;
    AutoFlushTimeOut := 10;
  end;
  Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','AdventureWorks2008R2','','');
  try
    //Props.ConnectionStringDialogExecute;
    Conn := Props.NewConnection;
    try
      Conn.Connect;
      Query := Conn.NewStatement;
      try
        Query.Execute('select * from Person.Address',true,[]);
        F := TFileStream.Create(ChangeFileExt(paramstr(0),'.json'),fmCreate);
        try
          Query.FetchAllToJSON(F,false);
        finally
          F.Free;
        end;
      finally
        Query.Free;
      end;
    finally
      Conn.Free;
    end;
  finally
    Props.Free;
  end;
end.

and the associated log :

D:\Dev\Temp\TestOleDB.exe 0.0.0.0 (2011-06-25)
Host=HPENVY15 User=User CPU=8*9-6-7685 OS=13.1=6.1.7601 Wow64=1 Freq=1558710
TSynLog(01E0A850) 1.14 2011-06-25 13:54:16

20110625 13541610  +    TOleDBConnection(01E29100).00075A37 SynOleDB.TOleDBConnection.Create (2538) 
20110625 13541610 info      null
20110625 13541610  -    TOleDBConnection(01E29100).00075AC7 SynOleDB.TOleDBConnection.Create (2544) 
20110625 13541610  +    TOleDBConnection(01E29100).000757B8 SynOleDB.TOleDBConnection.Connect (2502) 
20110625 13541635  -    TOleDBConnection(01E29100).00075987 SynOleDB.TOleDBConnection.Connect (2530) 
20110625 13541635  +    TOleDBStatement(01DCC330).00073A66 SynOleDB.TOleDBStatement.Create (1880) 
20110625 13541635  -    TOleDBStatement(01DCC330).00073B1B SynOleDB.TOleDBStatement.Create (1888) 
20110625 13541635  +    TOleDBStatement(01DCC330).000747CD SynOleDB.TOleDBStatement.Execute (2177) 
20110625 13541635 SQL       TOleDBStatement(01DCC330) select * from Person.Address
20110625 13541635  -    TOleDBStatement(01DCC330).00074CEE SynOleDB.TOleDBStatement.Execute (2261) 
20110625 13541635  +    TOleDBStatement(01DCC330).CreateAccessor
20110625 13541635  -    TOleDBStatement(01DCC330).CreateAccessor
20110625 13541650  +    TOleDBStatement(01DCC330).000750C3 SynOleDB.TOleDBStatement.Destroy (2329) 
20110625 13541650 info      TOleDBStatement(01DCC330) Total rows = 19614
20110625 13541650  -    TOleDBStatement(01DCC330).00075147 SynOleDB.TOleDBStatement.Destroy (2335) 
20110625 13541650  +    TOleDBConnection(01E29100).00075B41 SynOleDB.TOleDBConnection.Destroy (2550) 
20110625 13541650  +        TOleDBConnection(01E29100).00075BF0 SynOleDB.TOleDBConnection.Disconnect (2562) 
20110625 13541650  -        TOleDBConnection(01E29100).00075C40 SynOleDB.TOleDBConnection.Disconnect (2568) 
20110625 13541650  -    TOleDBConnection(01E29100).00075BAE SynOleDB.TOleDBConnection.Destroy (2556)

You can specify parameters, bound to the request, as such:

        Query.Execute('select * from Person.Customer where Name like ?',true,['B%']);

Or using direct Bind*() methods.

There is also a TQuery class defined, which is able to mimic basic TQuery VCL methods:

 Q := TQuery.Create(aSQLDBConnection);
 try
   Q.SQL.Clear; // optional
   Q.SQL.Add('select * from DOMAIN.TABLE');
   Q.SQL.Add('  WHERE ID_DETAIL=:detail;');
   Q.ParamByName('DETAIL').AsString := '123420020100000430015';
   Q.Open;
   Q.First;    // optional
   while not Q.Eof do begin
     assert(Q.FieldByName('id_detail').AsString='123420020100000430015');
     Q.Next;
   end;
   Q.Close;    // optional
 finally
   Q.Free;
 end;

This class can mimic basic TQuery VCL methods, but won't need any BDE installed, and will be faster for field and parameters access than the standard TDataSet based implementation; in fact, OleDB replaces the BDE or the DBExpress layer!
- Since there is no underlying TDataSet, you can't have read and write access, or use the visual DB components of the VCL: it's limited to direct emulation of low-level SQL as in the above code, with one-direction retrieval (e.g. the Edit, Post, Append, Cancel, Prior, Locate, Lookup methods do not exist within this class);
- This class is Unicode-ready even before Delphi 2009 (via the TQueryValue AsWideString method), will natively handle Int64/TBytes field or parameter
data, and will have less overhead than the standard DB components of the VCL;
- You should better use TSQLDBStatement instead of this wrapper, but having such code-compatible TQuery replacement could make easier some existing code upgrade (e.g. to avoid deploying the deprecated BDE, generate smaller executable, access any OleDB database without paying a big fee, avoid rewriting a lot of existing code lines of a big application...).

SynOleDB implementation has been made with several points:
- Tested with SQL Server 2008 and Oracle 11 providers from Microsoft and Oracle;
- Ability to be truly Unicode, even with pre-Unicode version of Delphi (like Delphi 7 or 2007);
- Could access any local or remote Database, from any version of Delphi (even the Delphi 7 personal or the Turbo Explorer editions), just for free;
- Handle NULL or BLOB content for parameters and results;
- Avoid most memory copy or unnecessary allocation: we tried to access the data directly from the retrieved data buffer, just as given from OleDB;
- Was therefore designed to achieve the better performance: most time is spent in OleDB: the code layer added to the OleDB customer is very thin;
- True OOP architecture, to be used with any OleDB provider (allowing custom parameters or such), and even without OleDB (in the future, direct access to any DB client could be used);
- Could be safely used in a multi-threaded application/server (with one TOleDBConnection per thread);
- Allow parameter bindings of requests, with fast access to any parameter or column name (thanks to TDynArrayHashed);
- Direct JSON content creation, with no temporary data copy nor allocation;
- Designed to be used with our mORMot ORM, but could be used stand-alone (a full Delphi 7 client executable is just about 200 KB), or even in any existing Delphi application, thanks to the TQuery light wrapper.

Nice for just one tiny unit, isn't it?
To download the latest version from our source code repository:
http://synopse.info/fossil/finfo?name=SynOleDB.pas
Feedback and comments are welcome.

ab · 2011-06-27 19:43:10

To download the source code:

1) go to http://synopse.info/fossil/finfo?name=SynOleDB.pas

2) click on Use anonymous login

3) click "Fill out catcha" button then "Login"

4) then click of the wanted version of the file (a link like [f9db7307d5fb1245] )

5) click on the "Download" while link on the top of the page, just against "Hex" and below the main menu

6) go to http://synopse.info/fossil/dir?ci=tip and do the same for the other common needed files (like synopse.info syncommons.pas synlz.pas - but other files, e.g. the one in SQLITE3 folder are not needed at all)

Compile from Delphi 6 up to XE.
Open Source, release a MPL/GPL/LGPL license.

It's still in pre-release: don't hesitate to post some feedback in this forum, and always retrieve the latest version of the units.

ab · 2011-06-29 12:48:38

New features:
• new ISQLDBRows interface
• allow thread-safe creation of connections or statements
• optional log SQL content will now write the bound parameter values in the trace
See http://synopse.info/fossil/info/7db654af37

You can now write such code:

procedure WriteFamily(const aName: RawUTF8);
var I: ISQLDBRows;
begin
  I := MyConnProps.Execute('select * from table where name=?',[aName]);
  while I.Step do
    writeln(I['FirstName'],' ',DateToStr(I['BirthDate']));
end;

ab · 2011-07-01 12:39:55

The magic of late binding

We implemented late binding access of column values, via a custom variant time. It uses the internal mechanism used for Ole Automation, here to access column content as if column names where native object properties.
The resulting Delphi code to write is just clear and obvious:

Props := TOleDBMSSQLConnectionProperties.Create('.\\SQLEXPRESS','AdventureWorks2008R2','','');
procedure TestISQLDBRowsVariant;
var Row: Variant;
begin
OleDBSynLogClass.Enter;
with Props.Execute('select * from Sales.Customer where AccountNumber like ?',
['AW000001%'],@Row) do
while Step do
assert(Copy(Row.AccountNumber,1,8)='AW000001');
end;

Note that Props.Execute returns an ISQLDBRows interface, so the code above will initialize (or reuse an existing) thread-safe connection (OleDB uses a per-thread model), initialize a statement, execute it, access the rows via the Step method and the Row variant, retrieving the column value via a direct Row.AccountNumber statement.
The above code is perfectly safe, and all memory will be release with the reference count garbage-collector feature of the ISQLDBRows interface. You don't have to add any try..finally Free end statements in your code.
This is the magic of late-binding in Delphi.

The low-level Variants unit is (optionally) hacked to use a dedicated direct call of our properties. In practice, this late binding is as fast using a standard property like Column['ColumnName'] than using the variant variation Row.ColumnName.
This speed increase has also been shared with the late-binding access of values for our Big Table unit.

ab · 2011-07-01 15:24:29

Faster variant late binding

For both our SynOleDB and SynBigTable units, we allow late-binding of data row values, using a variant and direct named access of properties. Thanks to this unique feature (as far as I know in the Delphi database world),

This allows clear and valid code as such:

var Customer: Variant;
begin
with Props.Execute(
'select * from Sales.Customer where AccountNumber like ?',
['AW000001%'],@Customer) do
while Step do
assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

In practice, this code is slower than using a standard property based access, like this:

while Step do
assert(Copy(Column['AccountNumber'],1,8)='AW000001');

But the first version, using late-binding of column name, just sounds more natural.

Of course, since it's late-binding, we are not able to let the compiler check at compile time for the column name. If the column name in the source code is wrong, an error will be triggered at runtime only. But

The default implementation was sometimes slow for our purpose. Since it has to deal with Ole Automation, and because it's fun, we hack the VCL implementation to provide a lighter and faster version for our custom variant types.

The fastest version

First of all, let's see the fastest way of accessing the row content.

In all cases, using the textual version of the column name ('AccountNumber') is slower than using directly the column index. Even if our SynOleDB library uses a fast lookup using hashing, the following code will always be faster:

var Customer: Integer;
begin
with Props.Execute(
'select * from Sales.Customer where AccountNumber like ?',
['AW000001%']) do begin
Customer := ColumnIndex('AccountNumber');
while Step do
assert(Copy(ColumnString(Customer),1,8)='AW000001');
end;
end;

But to be honest, after profiling, most of the time is spend in the Step method, especially in fRowSet.GetData. In practice, I was not able to notice any speed increase worth mentioning, with the code above.

Our name lookup via a hashing function (TDynArrayHashed) just does it purpose very well.

Variant Late binding

So, how does the variant type used by Ole Automation and our custom variant types (i.e. TSynTableVariantType or TSQLDBRowVariantType) handle their properties access?

Behind the scene, the Delphi compiler calls the DispInvoke function, as defined in the Variant.pas unit.

The default implementation of this DispInvoke is some kind of slow:
- It uses a TMultiReadExclusiveWriteSynchronizer under Delphi 6, which is a bit over-sized for its purpose: since Delphi 7, it uses a lighter critical section;
- It makes use of WideString for string handling (not at all the better for speed), and tends to define a lot of temporary string variables;
- For the getter method, it always makes a temporary local copy during process, which is not useful for our classes.

Fast and furious

So we rewrite the DispInvoke function with some enhancements in mind:
- Will behave exactly the same for other kind of variants, in order to avoid any compatibility regression, especially with Ole Automation;
- Will quick intercept our custom variant types (as registered via the global SynRegisterCustomVariantType function), and handle those with less overhead: no critical section nor temporary WideString allocations are used.

Implementation

Here is the resulting code, from our SynCommons unit:

procedure SynVarDispProc(Result: PVarData; const Instance: TVarData;
CallDesc: PCallDesc; Params: Pointer); cdecl;
const DO_PROP = 1; GET_PROP = 2; SET_PROP = 4;
var i: integer;
Value: TVarData;
Handler: TCustomVariantType;
begin
if Instance.VType=varByRef or varVariant then // handle By Ref variants
SynVarDispProc(Result,PVarData(Instance.VPointer)^,CallDesc,Params) else begin
if Result<>nil then
VarClear(Variant(Result^));
case Instance.VType of
varDispatch, varDispatch or varByRef,
varUnknown, varUnknown or varByRef, varAny:
// process Ole Automation variants
if Assigned(VarDispProc) then
VarDispProc(pointer(Result),Variant(Instance),CallDesc,@Params);
else begin
// first we check for our own TSynInvokeableVariantType types
if SynVariantTypes<>nil then
for i := 0 to SynVariantTypes.Count-1 do
with TSynInvokeableVariantType(SynVariantTypes.List[ i ]) do
if VarType=TVarData(Instance).VType then
case CallDesc^.CallType of
GET_PROP, DO_PROP: if (Result<>nil) and (CallDesc^.ArgCount=0) then begin
IntGet(Result^,Instance,@CallDesc^.ArgTypes[0]);
exit;
end;
SET_PROP: if (Result=nil) and (CallDesc^.ArgCount=1) then begin
ParseParamPointer(@Params,CallDesc^.ArgTypes[0],Value);
IntSet(Instance,Value,@CallDesc^.ArgTypes[1]);
exit;
end;
end;
// here we call the default code handling custom types
if FindCustomVariantType(Instance.VType,Handler) then
TSynTableVariantType(Handler).DispInvoke(
{$ifdef DELPHI6OROLDER}Result^{$else}Result{$endif},
Instance,CallDesc,@Params)
else raise EInvalidOp.Create('Invalid variant invoke');
end;
end;
end;
end;

Our custom variant types have two new virtual protected methods, named IntGet/IntSet, which are the getter and setter of the properties. They will to the property process, e.g. for our OleDB column retrieval:

procedure TSQLDBRowVariantType.IntGet(var Dest: TVarData;
const V: TVarData; Name: PAnsiChar);
var Rows: TSQLDBStatement;
begin
Rows := TSQLDBStatement(TVarData(V).VPointer);
if Rows=nil then
EOleDBException.Create('Invalid SQLDBRowVariant call');
Rows.ColumnToVariant(Rows.ColumnIndex(RawByteString(Name)),Variant(Dest));
end;

As you can see, the returned variant content is computed with the following method:

function TOleDBStatement.ColumnToVariant(Col: integer;
var Value: Variant): TSQLDBFieldType;
const FIELDTYPE2VARTYPE: array[TSQLDBFieldType] of Word = (
varEmpty, varNull, varInt64, varDouble, varCurrency, varDate,
{$ifdef UNICODE}varUString{$else}varOleStr{$endif}, varString);
var C: PSQLDBColumnProperty;
V: PColumnValue;
P: pointer;
Val: TVarData absolute Value;
begin
V := GetCol(Col,C);
if V=nil then
result := ftNull else
result := C^.ColumnType;
VarClear(Value);
Val.VType := FIELDTYPE2VARTYPE[result];
case result of
ftInt64, ftDouble, ftCurrency, ftDate:
Val.VInt64 := V^.Int64; // copy 64 bit content
ftUTF8: begin
Val.VPointer := nil;
if C^.ColumnValueInlined then
P := @V^.VData else
P := V^.VAnsiChar;
SetString(SynUnicode(Val.VPointer),PWideChar(P),V^.Length shr 1);
end;
ftBlob: begin
Val.VPointer := nil;
if C^.ColumnValueInlined then
P := @V^.VData else
P := V^.VAnsiChar;
SetString(RawByteString(Val.VPointer),PAnsiChar(P),V^.Length);
end;
end;
end;

This above method will create the variant content without any temporary variant or string. It will return TEXT (ftUTF8) column as SynUnicode, i.e. into a generic WideString variant for pre-Unicode version of Delphi, and a generic UnicodeString (=string) since Delphi 2009. By using the fastest available native Unicode string type, you will never loose any Unicode data during charset conversion.

Hacking the VCL

In order to enable this speed-up, we'll need to change each call to DispInvoke into a call to our custom SynVarDispProc function.

With Delphi 6, we can do that by using GetVariantManager /SetVariantManager functions, and the following code:

GetVariantManager(VarMgr);
VarMgr.DispInvoke := @SynVarDispProc;
SetVariantManager(VarMgr);

But since Delphi 7, the DispInvoke function is hard-coded by the compiler into the generated asm code. If the Variants unit is used in the project, any late-binding variant process will directly call the _DispInvoke private function of Variants.pas.

First of all, we'll have to retrieve the address of this _DispInvoke. We just can't use _DispInvoke or DispInvoke symbol, which is not exported by the Delphi linker... But this symbol is available from asm!

So we will first define a pseudo-function which is never called, but will be compiled to provide a pointer to this _DispInvoke function:

procedure VariantsDispInvoke;
asm
call Variants.@DispInvoke;
end;

Then we'll compute the corresponding address via this low-level function, the asm call opcode being $E8, followed by the relative address of the sub-routine:

function GetAddressFromCall(AStub: Pointer): Pointer;
begin
if AStub=nil then
result := AStub else
if PBYTE(AStub)^ = $E8 then begin
Inc(PtrInt(AStub));
Result := Pointer(PtrInt(AStub)+SizeOf(integer)+PInteger(AStub)^);
end else
Result := nil;
end;

And we'll patch this address to redirect to our own function:

RedirectCode(GetAddressFromCall(@VariantsDispInvoke),@SynVarDispProc);

The resulting low-level asm will just look like this at the call level:

TestOleDB.dpr.28: assert(Copy(Customer.AccountNumber,1,8)='AW000001');
00431124 8D45D8 lea eax,[ebp-$28]
00431127 50 push eax
00431128 6828124300 push $00431228
0043112D 8D45E8 lea eax,[ebp-$18]
00431130 50 push eax
00431131 8D45C4 lea eax,[ebp-$3c]
00431134 50 push eax
00431135 E86ED1FDFF call @DispInvoke

It will therefore call the following hacked function:

0040E2A8 E9B3410100 jmp SynVarDispProc
0040E2AD E853568B5D call +$5d8b5653
... (previous function content, never executed)

That is, it will jump (jmp) to our very own SynVarDispProc, just as expected.

In fact, the resulting code is very close to a direct ISQLDBRows.Column['AccountNumber'] call. Using late-binding can be both fast on the execution side, and easier on the code side.

On real data, e.g. accessing 500,000 items in our SynBigTable benchmark, our new SynVarDispProc implementation is more than two time faster than the default Delphi implementation, with Delphi 2010.
Worth the result, isn't it?

andrewdynamo · 2011-07-04 12:28:04

I did some testing against our database with low level ADO and SynOleDB:

=========================

ADO, client side cursor:
Execute: 15.894,00ms
1153092 Rows in 31.828,00ms

ADO, server side cursor:
Execute: 2.473,00ms
-1 Rows in 30.212,00ms

SynOLEDB
Execute: 2.421,00ms
1153092 Rows in 18.204,00ms

=========================

ADO, client side cursor:
Execute: 385,84ms
7727 Rows in 147,58ms

ADO, server side cursor:
Execute: 198,28ms
-1 Rows in 532,66ms
Second time:
Execute: 198,00ms
-1 Rows in 176,24ms <--- ???

SynOLEDB
Execute: 197,56ms
7727 Rows in 480,42ms

=========================

My code:

Var i: integer; s: string;

cmd := TADOCommand.Create(Self);
cmd.Connection := Self.ADOConnection1;
//ADOConnection1.CursorLocation := clUseServer;
cmd.CommandText := 'select top 1 * from view1';
recordset:= cmd.Execute(iRows, EmptyParam);
while not recordset.EOF do
begin
for i := 0 to recordset.Fields.Count - 1 do
s := recordset.Collect(i);
recordset.MoveNext;
end;

---

Query := Conn.NewStatement;
Query.Execute('select * from view1', True, []);

while Query.Step do
begin
for i := 0 to Query.ColumnCount - 1 do
s := Query.ColumnVariant(i);
end;

=========================

So for many records (>10.000) SynOleDB seems faster (18s vs 30s), but ADO seems faster at small records sets (680ms vs 532ms client side)
(ADO caches results, because second time is a lot faster sometimes?)

Last edited by andrewdynamo (2011-07-04 12:32:50)

ab · 2011-07-04 12:54:35

andrewdynamo wrote:

So for many records (>10.000) SynOleDB seems faster (18s vs 30s), but ADO seems faster at small records sets (680ms vs 532ms client side)
(ADO caches results, because second time is a lot faster sometimes?)

ADO could cache results, as far as I know.
But in all cases, we handle caching at the ORM level, not the DB level.

About row speed, 18 sec instead of 30 sec do make sense, since ADO is a layer over OleDB.

About small record set, I don't understand why ADO is faster. AFAIK ADO do create an IAccessor per each column, whereas we create a global IAccessor for all columns at once. In all cases, 500-600 ms seems a lot for small records sets.

Did you use a local database?
IMHO to have a fair benchmark, you should use a local database.

ab · 2011-07-04 15:49:04

I just made similar tests, with direct ADO access.

For a small result of 2393 rows, corresponding to 151 KB of JSON data:

0000000000265970  -    00073D3C TestOleDB.TestISQLDBRowsColumn (59)  00.070.038
0000000000285191  -    00073EE4 TestOleDB.TestISQLDBRowsVariant (70)  00.070.713
00000000002C4040  -    00073AF3 TestOleDB.ADOTest (37)  00.077.282
00000000002E6373  -    00073AF3 TestOleDB.ADOTest (37)  00.073.380

The time is in ms (70 ms for SynOleDB, 73/73 for ADO).
It's tested with a remote Oracle 11g database.

I run the ADO test twice, to see if the caching difference.
The 2nd request was a bit faster than the 1st, but slower than SynOleDB.
Sometimes, the 2nd request is slower than the 1st, for bigger results - but I don't know why (some kind of garbage collection/memory release).

So even for a small records set, SynOleDB seems always faster.

For a big amount of data (14 MB of JSON, for 240288 rows):

000000000099DA71  -    000742AF TestOleDB.TestJSON (130)  04.209.928
0000000002148C9A  -    00073F70 TestOleDB.TestSynOleDB (83)  04.232.666
0000000002A9EF8A  -    00073A6A TestOleDB.ADOTest (38)  05.281.028
000000000344286B  -    00073A6A TestOleDB.ADOTest (38)  05.549.811
0000000003C11F72  -    0007410A TestOleDB.TestTQuery (110)  04.498.562

What is interesting is that TestJSON is faster than all other test functions, whereas it produced 14 MB of JSON data in a file.
The TestSynOleDB still make a conversion to Variant for each column, so is a little bit slower than our method creating JSON data directly from the OleDB binary buffer.

In all cases, most of the time is spent in the OleDB provider, and waiting for data from the remote DB (the CPU is low, about 10%, during the process - it just keep waiting from data from network).
In term of pure client process, SynOleDB is definitively lighter and faster.
It does make perfectly sense, since ADO is a layer over OleDB. So direct access to OleDB should always be faster.

And in my results, I didn't find any evidence of caching in ADO.
It will also depend on the provider. I used the latest version from Oracle for this test.

I guess there was something wrong with your timing.

In all cases, the big difference will be in comparison with TDataSet.
You can see than our TQuery wrapper is really fast: there is almost no overhead in this wrapper according to the original direct TOleDBStatement run.
This TQuery will bypass at least three additional layers: ADO, DBExpress and TDataSet. Memory use will also be much lower.

Here is the code used for this test (big amount of data version):

var Props: TOleDBOracleConnectionProperties;

procedure TestADO;
var C: TADOConnection;
procedure ADOTest;
var R: _Recordset;
    n, i: integer;
begin
  OleDBSynLogClass.Enter;
  with TADOCommand.Create(nil) do
  try
    Connection := C;
    CommandText := 'select * from tableName WHERE ID_TOKEN<>''409020020100000430015''';
    R := Execute;
    n := 0;
    while not R.EOF do begin
      for i := 0 to R.Fields.Count-1 do
        R.Collect[i];
      R.MoveNext;
      inc(n);
    end;
    OleDBSynLogClass.Add.Log(sllInfo,'RowCount=%',n);
  finally
    Free;
  end;
end;
begin
  C := TADOConnection.Create(nil);
  try
    C.ConnectionString := Props.ConnectionString;
    C.Open;
    ADOTest;
  finally
    C.Free;
  end;
end;

procedure TestISQLDBRowsColumn;
var I: ISQLDBRows;
begin
  OleDBSynLogClass.Enter;
  // should be in a procedure otherwise the statement won't be released
  // when the connection closes
  I := Props.Execute('select * from tableName WHERE ID_TOKEN<>?',
        ['409020020100000430015']);
  while I.Step do
    assert(I['ID_TOKEN']<>'409020020100000430015');
end;

procedure TestISQLDBRowsVariant;
var Row: Variant;
begin
  OleDBSynLogClass.Enter;
  with Props.Execute('select * from tableName WHERE ID_TOKEN<>?',
        ['409020020100000430015']) do begin
    Row := RowData;
    while Step do
      assert(Row.id_token<>'409020020100000430015');
  end;
end;

procedure TestSynOleDB;
var I: ISQLDBRows;
    f: integer;
begin
  OleDBSynLogClass.Enter;
  I := Props.Execute('select * from tableName WHERE ID_TOKEN<>?',
        ['409020020100000430015']);
  while I.Step do
    for f := 0 to I.ColumnCount-1 do
      I.ColumnVariant(f);
end;

var Conn: TSQLDBConnection;

procedure TestTQuery;
var Q: TQuery;
    i: integer;
begin
  OleDBSynLogClass.Enter;
  Q := TQuery.Create(Conn);
  try
    Q.SQL.Clear;
    Q.SQL.Add('select * from tableName');
    Q.SQL.Add('  WHERE ID_TOKEN<>:token;');
    Q.ParamByName('TOKEN').AsString := '409020020100000430015';
    Q.Open;
    Q.First;
    while not Q.Eof do begin
      for i := 0 to Q.FieldCount-1 do
        Q.Fields[i].AsVariant;
      Q.Next;
    end;
    Q.Close;
  finally
    Q.Free;
  end;
end;

procedure TestJSON;
var Query: TSQLDBStatement;
    F: TStream;
begin
  OleDBSynLogClass.Enter;
  Query := Conn.NewStatement;
  try
    Query.Execute('select * from tableName WHERE ID_TOKEN<>?',
      true,['409020020100000430015']);
    F := TFileStream.Create(ChangeFileExt(paramstr(0),'.json'),fmCreate);
    try
      Query.FetchAllToJSON(F,false);
    finally
      F.Free;
    end;
  finally
    Query.Free;
  end;
end;

andrewdynamo · 2011-07-05 06:28:44

My timing was correct :-) I tested it several times, even using QueryPerformanceCounter (TStopWatch) instead of millisecondspan.
But I use SQL Server 2008 R2, so probably it has some kind of caching on its own (not ADO generic)?

I think timing is also dependent on amount of null/integer/string data, because my little bit slower SynOleDb test had a lot of string data (which is converted from UTF8 in SynOleDB??).

By the way: SynOleDB seems to have much higher network usage! It used several MB/s during data retrieval, but ADO had much lower amount, like 100Kb/s?? Maybe some kind of compression? I read all column data to be sure everything is fetched.

ab · 2011-07-05 06:46:53

My above tests use our Logging high-resolution timer, calling the QueryPerformanceCounter API.

I'll try with SQL Server 2008 R2 soon.

Default Variant retrieval by SynOleDB will use a WideString/OleStr, with no UTF8 conversion (there is a dedicated version retrieving the content directly from the OleDB buffer).
WideString is slower than a Delphi string, but it should be the same for ADO, which returns a variant, i.e. also an OleStr for text.

The main diff with ADO is that AFAIK it creates an Accessor per column, whereas SynOleDB creates an Accessor for the whole table.

Or perhaps some diverse initialization parameters.
What are your connection parameters for ADO?
I used the same connection string for both SynOleDB and ADO, and in this case, SynOleDB was always faster than ADO.

bert · 2011-07-06 19:06:19

Hi,

Would there be a posibility to get SynOleDB running with FPC?
Are there plans to add support for FPC?

Kind regards,
Bert

ab · 2011-07-07 07:05:04

I tried to make it FPC compatible, and even 64 bit ready (e.g. for pointer arithmetic and data alignment).
But I didn't test it.

The SynOleDB relies on SynCommons, which is somewhat huge.
We would have to run the SynCommons unitary tests first, then test SynOleDB.

You may take a try, and provide patches to make it work with FPC.

ab · 2011-08-05 13:31:24

ab wrote:

I'll try with SQL Server 2008 R2 soon.

When testing against a SQL Server 2008 database, our OleDB classes are faster than the standard ADO classes.

ab · 2011-09-23 14:24:34

Back to this thread's subject, the late-binding DispInvoke optimization is not working as such with Delphi XE2.
They changed the RTL internals, so current implementation just crashes.

I did not buy the full version of XE2 (a lot of money by the way - and I don't make money of my Open Source units), only tried to run it with the trial version.
In SynCommons.pas, the trick will just be ignored for XE2, and the default (2 times slower) implementation will work, just as expected.

ab · 2012-02-01 13:23:00

It is worth reading official announcement from Microsoft at http://blogs.msdn.com/b/sqlnativeclient … ccess.aspx

Microsoft is Aligning with ODBC for Native Relational Data Access
...
We encourage you to adopt ODBC in the development of your new and future versions of your application. You don’t need to change your existing applications using OLE DB, as they will continue to be supported on Denali throughout its lifecycle. While this gives you a large window of opportunity for changing your applications before the deprecation goes into effect, you may want to consider migrating those applications to ODBC as a part of your future roadmap.

They created OLE DB to make a "pure Windows" concurrent of ODBC.
We were told that OLE DB was much better than ODBC, by Micro$oft evangelists.
Now they are going back to ODBC because Cross-Platform is now mandatory. This is all marketing and money here. This won't be the first abandoned technology from Microsoft (or any other company).

Open source is another world, even if money is still within, you can always fork a dead project to continue supporting it.

In fact, OleDB was mostly to be used for MS SQL. Oracle provider is buggy - no cry for me...

I'm working on adding ODBC support for our SynDB classes.
This will be necessary for (Linux and) Mac OS support of DBs other than Oracle or SQLite3.
See http://synopse.info/fossil/finfo?name=SynDBODBC.pas

ab · 2012-08-10 15:02:34

That's it: ODBC is now officially supported within mORMot.

The SynDBODBC.pas unit is now working, and able to access any database using direct ODBC calls (with no OleDB gateway).
See http://synopse.info/fossil/info/3efaf59e06

As a consequence, our free SynDBExplorer tool is able to connect via ODBC.
See http://synopse.info/fossil/info/f324d469ee
It is amazing that how easy it is to add a new provider: just add the TODBCConnectionProperties class, and that's it!

I have also updated documentation about the new ODBC direct connection access.
http://synopse.info/fossil/info/0980301f72

Performance is quite good, but there are still place for improvement:

implement array binding of parameters;
implement row-wise binding when all columns are inlined.

Everything is ready to add those features. If you need extra-speed, please ask!

ab · 2014-03-29 15:49:57

DispInvoke() now works with Delphi XE2.
It fixes also the "uppercase" property name issue.
See http://blog.synopse.info/post/2014/03/2 … XE2-and-up

mORMot Open Source

#1 2011-06-27 14:41:19