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
or
(fRowSet as IAccessor).CreateAccessor
.
In practice, I was not able to notice any speed increase worth mentioning, with
the code above, even for large loops.
Our name lookup via a hashing function from a dynamic array just does its work 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 such late-binding property
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 faster
critical section instead;
- 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 on
the stack;
- 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:
- Shall behave exactly the same for other kind of variants, in order to avoid
any compatibility regression, especially with Ole Automation;
- Shall quickly intercept our custom variant types (as registered via the
global SynRegisterCustomVariantType
function), and handle those
with less overhead: no critical section nor temporary WideString /
Variant
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.
Results
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? :)
In fact, the resulting code speed 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.
Delphi has wonders in its heart, even since old versions!
Feedback and comments are welcome in our
forum!