UniTable 5.2.6 D7 on SQLServer: Possibly more performance when closing partly fetched Tables

[Rahalph]

Hello again,

when accessing large tables (several million records in a table with about 50 columns) on the local network, I have to set "FetchAll=False" to make the open time endurable for the users. As expected the first 25 records are fetched and displayed. If the user closes the table before moving to the last record, the UniTable.Close method takes several minutes. I assumed the Table internally fetches all records before it really closes.

Because I didn't find any property which helped me closing the table faster, I had a closer look at UniDac's source code and Microsoft's OleDB descriptions. I found an "ISSAbort" interface to abort a command. I've implemented that interface into the OLEBD provider units of UniDac (see code snippets below) and achieved a very fast close of the tables no matter whether they are fetched or not.

Because I do not really catch on all the source code and my implementation is a bit "quick an dirty" I would love somebody of the UniDac development to have a look at what I did. Maybe I forgot to make a change somewhere or maybe it cannot used generally this way. But if it is okay, I would be glad if you could implement this huge performance increase.

Thanks
Ralph


OLEDBIntfUni.pas:

// *********************************************************************//
// Interface: IMultipleResults
// GUID: {0C733A90-2A1C-11CE-ADE5-00AA0044773D}
// *********************************************************************//
IMultipleResults = interface(IUnknown)
['{0C733A90-2A1C-11CE-ADE5-00AA0044773D}']
function GetResult(
const punkOuter: IUnknown;
reserved: DBRESULTFLAG;
const riid: TGUID;
out pcRowsAffected: DBROWCOUNT;
out ppRowset: IUnknown): HResult; stdcall;
end;

// RR_Close+
// *********************************************************************//
// Interface: ISSAbort
// GUID: {5CF4CA15-EF21-11d0-97E7-00C04FC2AD98}
// *********************************************************************//
ISSAbort = interface(IUnknown)
['{5CF4CA15-EF21-11d0-97E7-00C04FC2AD98}']
function Abort(): HRESULT; stdcall;
end;
// RR_Close-

// *********************************************************************//
// Interface: ICommandPrepare
// GUID: {0C733A26-2A1C-11CE-ADE5-00AA0044773D}
// *********************************************************************//
ICommandPrepare = interface(IUnknown)
['{0C733A26-2A1C-11CE-ADE5-00AA0044773D}']
function Prepare(cExpectedRuns: UINT): HResult; stdcall;
function Unprepare: HResult; stdcall;
end;



OLEDBCUni.pas:

IID_IMultipleResults: TGUID = '{0C733A90-2A1C-11CE-ADE5-00AA0044773D}';
// RR_Close+
IID_ISSAbort: TGUID = '{5CF4CA15-EF21-11d0-97E7-00C04FC2AD98}';
// RR_Close-
IID_IConvertType: TGUID = '{0C733A88-2A1C-11CE-ADE5-00AA0044773D}';



OLEDBAccessUni.pas:

TOLEDBCommand = class (TCRCommand)
protected
...
FICommandProperties: ICommandProperties;
// RR_Close+
FICommandAbort: ISSAbort;
// RR_Close-


procedure TOLEDBCommand.Execute(Iters: integer = 1);
...
if FRequestIUnknown and (IUnk <> nil) then begin
if FRequestMultipleResults then
QueryIntf(IUnk, {$IFDEF CLR}IMultipleResults{$ELSE}IID_IMultipleResults{$ENDIF}, FIMultipleResults)
else // This is a server cursor or DbxSda call
FIUnknown := IUnk;
// RR_Close+
// Get the interface only for real Tables/Queries and release the old one if already got
if (Pos ('SELECT ', Uppercase (FSQL)) > 0) then begin
FICommandAbort:= nil;
QueryIntf(IUnk, {$IFDEF CLR}ISSAbort{$ELSE}IID_ISSAbort{$ENDIF}, FICommandAbort);
end;
// RR_Close-
IUnk := nil;
end;


procedure TOLEDBRecordSet.ReleaseCommandInterfaces;
begin
FCommand.ReleaseInterfaces;
// RR_Close+
// Release the interface when ExecCommand done, if not a real Table/Query
if FCommand.CommandType <> ctCursor then
FCommand.FICommandAbort:= nil;
// RR_Close-
end;


procedure TOLEDBRecordSet.ReleaseRecordSetInterfaces;
begin
// RR_Close+
// Abort command (fetch) if records are pending
if ((FCommand.FICommandAbort <> nil) and
(FCommand.GetCursorState = csFetching)) then begin
FCommand.FICommandAbort.Abort;
end;
// Always release the interface
FCommand.FICommandAbort:= nil;
// RR_Close-
...

[AlexP]

hello,

Thank you for the sample, we will consider the possibility to use this code to solve the problem.