[vwnc] Glorp and Array Binding for Oracle

Interesting. I'm surprised it worked as well as that. Glorp goes to a fair bit of effort to make that sort of thing possible, although it also gets other benefits from the same work. In particular, it takes all of the objects to be written in a particular transaction, generates all the row information, and then sorts the rows by type and based on the foreign key constraints in order to write as many rows as possible in the same operation. 

At 02:19 PM 3/7/2009, Henrik Johansen wrote:
  

I did something of the same for Lens -> Oracle DB, modifying the addAll: to perform array binding (per type of course) of the objects instead of committing them one by one... Strangely enough, this speeded things up by alot!

I can't guarantee that it'll always work, but for us it's left no visible side-effects, been in production for 1/2 year or so with no reported problems. Can't really remember if I put any constraints on the inputs anymore, guess I should comment abit more :)
Named it addAllFast: as it wasn't really needed for all operations, and I'd like to isolate potental problems as much as possible.

Cheers,
Henry

On 07.03.2009 15:59, Joachim Geidel wrote:
    

Hello everybody,

I need some advice from people who know the inner workings of Glorp. First,
let me describe the background.

I am currently confronted with an older VW application which is based on the
ObjectLens and uses an Oracle database. It has performance problems, because
the ObjectLens executes inserts and updates as separate statements for each
row.

The Oracle Call Interface and the OCI support array binding, i.e. executing
a single call of the OCIStmtExecute() function with arrays as parameters and
the "iters" parameter set to the number of elements of the arrays. The
ObjectLens does not support this, but will execute 1000 INSERT statements
when inserting 1000 rows, which is slower than using array binding for a
single insert by an incredibly large factor. It will also drive your DBA
nuts. ;-)

I looked inside the ObjectLens to see if it could be altered to use array
binding for Oracle databases, but that would not be easy. The code is a bit
too convoluted, with methods doing preprocessing, executing SQL, and
postprocessing for single rows. It's probably possible to refactor this, but
it won't be what I consider fun.

Another option would be porting the application to Glorp. However, when
looking into the code it seems that it supports array binding only for
INSERT statements (InsertCommands). The method comment of
RowBasedCommand>>supportsGroupWriting says:

"Return true if array binding or a similar mechanism can be used to execute
us with multiple commands at once. I think in general this can only ever
make sense for inserts (and possibly selects under some circumstances).
Updates may want to update only part of the fields."

IMO it is wrong that array binding only makes sense for INSERTS, at least
with an Oracle database. The Oracle Call Interface supports update and
delete commands with array binding AFAIK. E.g., one can delete multiple rows
with the statement
    DELETE FROM myTable WHERE someColumn = :1
by binding :1 to an array of values.

Also, Glorp limits the number of rows which will be passed to an INSERT
command by array binding to 1000 (RowBasedCommand>>tooBigForSingleWrite).
The OCI has no such limitation. Of course, binding more than 1000 rows to a
single call will allocate larger OracleBuffers, but if the array elements
are small (e.g. Integers) and there is enough memory available, it is no
problem at all to use array binding with many more elements in a bind array.

Another issue is that for each row (or group of rows when using array
binding), a new ExternalDatabaseSession is created, prepared, bound, and
executed. This is a bad practice (which could be fixed at the EXDI level in
VW, but I don't know about other dialects), because preparing the same
statement over and over again is an expensive operation which involves
parsing the SQL statement, computing an execution path etc. Oracle servers
will cache the results in the SGA, but this still requires unnecessary
network roundtrips and computations in the database server. It should be
implemented in a way which caches the result of the "prepare" step, and
binds and executes the ExternalDatabaseSession in a loop.

These limitations can seriously hamper the performance of Glorp based
applications when they have to process mass data, just as it was the case
with the ObjectLens.

So, what I would like to know is:

* Is my analysis concerning the behavior of Glorp correct?

* Would it be sufficient to alter the default return value of
supportsGroupWriting to true to enable array binding for UPDATE and DELETE
commands? Or would there be other things which need to be changed, e.g. in
DatabasePlatform and its subclasses?

* For UPDATEs, supporting array binding may not be trivial, depending on the
changes which have to be posted. Different objects may have changes in
different attributes. It would be possible to compute the set of all columns
which have a change for at least one row within a group of rows, and update
them all for all of the rows. When the number of rows is large enough and
array binding can be used, this will still be much faster than posting
dozens or hundreds of different single-row updates (not to speak of
thousands). How difficult would it be to change the Glorp implementation to
do this?

* How much effort would it be to reuse prepared ExternalDatabaseSessions in
Glorp?

Thanks in advance,

Joachim Geidel



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc


  
      

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc
    

--
Alan Knight [|], Engineering Manager, Cincom Smalltalk
[hidden email]
[hidden email]
http://www.cincom.com/smalltalk