idea to improve performance

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

Post Reply
CharlesMcAllister
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 25.10.2006, 19:25

idea to improve performance

Post by CharlesMcAllister »

I got an idea to improve performance when doing a lot of inserting into the database. The following code in ZDbcCachedResultSet will execute a query for every row being inserted just to get the row default values. If doing bulk inserts into the same table, it shouldn't be necessary to get these default values from the database for every row. The default values could be cached in a result set for re-use.

From the profiling i've done, it should improve performance quite a bit when inserting a lot into the same table, since it is spending just as long calculating the row defaults as it is actually posting the row inserts.

[delphi]
procedure TZAbstractCachedResultSet.InternalCommitRow(AIndex: Integer);
begin
...
{ Updates default field values. }
if NewRowAccessor.RowBuffer.UpdateType = utInserted then
CalculateRowDefaults(NewRowAccessor);
...
[/delphi]

If i write the feature to cache the default values, would anyone care to have this incorporated into Zeos? How would you prefer this to be written?
CharlesMcAllister
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 25.10.2006, 19:25

Post by CharlesMcAllister »

I stumbled on an already existing solution. Turns out if I always set the value for every column, then Zeos is smart enough not to get default values for the columns that have not been assigned a value.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

However, if you want to write the default values caching, go on... Maybe not everybody wants to set the values for every column... You know I'll study your proposal. Getting a cache at CachedResultsetLevel looks good. I'm just not sure it can be implemented on the abstract level to avoid per db implementations.

When you're coding : are you using an other server than mysql? I look for some people to implement the GetServerVersion and GetClientVersion functions for other databases. (if you look at it, do it in Testing branch version as I fixed some major issue with that yesterday)

Mark
CharlesMcAllister
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 25.10.2006, 19:25

Post by CharlesMcAllister »

I think i'll stick with my workaround for now. FWIW there's also a way to keep the Resolver from calculating defaults...

[delphi]
constructor TZGenericCachedResolver.Create(Statement: IZStatement;
Metadata: IZResultSetMetadata);
begin
...
FCalcDefaults := StrToBoolEx(DefineStatementParameter(Statement,
'defaults', 'true'));
...
end;
[/delphi]

I'm still evaluating ZeosLib to see if its going to do everything i need.
I'm currently working with Firebird, and MSSQL using 'ado' driver.
I'll take a look at the version methods (hopefully today).
Post Reply