Insert/select...

Hi all,

I have added a new SInsertSelect construct in SimpleORM.

This is a low level tool to generate insert into ... select ... style 
sql based on Metas and SQueryTransient.
This has the advantage of:
- using object oriented SRecordMeta and SFieldScalar to build your query
- delegating the work to the database, which will be the most efficient 
at this task

But keep in mind this is a low level trick, closer to jdbc than to an 
ORM tool.
In particular:
- this will bypass any validation done by 
SRecordInstance.onValidateRecord().

So that said, here is how it works... Lets say you have a sales order 
table, and a stats tables.
You can do the following:

SQueryTransient qryTrs = new SQueryTransient(new SQuery<Order>(Order.meta));
qryTrs.groupBy(Order.productId).groupBy(Order.month).avg(Order.amount).selectRaw("nextval('mysequence')");

SInsertSelect<Stats> insSel = new SInsertSelect(Stats.meta, qryTrs);
insSel.addField(Stats.productId).addField(Stats.month).addField(Stats.averageOrder).addField(Stats.id);

session.begin();
session.rawInsertSelectNoFlush(insSel);
session.commit();

Re: Adding batch update support in SimpleORM

Hi Noël,

in fact the SDriver must announce if it supports batching or not.
In SDriver, we use this code to tell that :

DatabaseMetaData dbmeta = session.jdbcConnection.getMetaData();
              if (dbmeta != null) {
                  if (dbmeta.supportsBatchUpdates()) {
                      return true;
                  }
              }

But if metadata lies, we can override that method and force public 
boolean supportsBatchUpdates to return false in SDriverXXXX.

Franck

Le 04/02/2013 15:05, Noel Grandin a écrit :
>
> On 2013-02-01 18:22, Franck Routier wrote:
>>
>> I just commited a modification that add jdbc batch support in SimpleROM.
>>
> Cool!
>
>> By default, everything works as before, so this should have no impact 
>> on running projects.
>>
> Good, because not all JDBC drivers support batching (e.g. H2).
>

Re: Adding batch update support in SimpleORM

Hi Anthony,

Le 02/02/2013 01:42, anthony berglas a écrit :

 

Excellent work. 

Thanks

(The batching is all wrong in JDBC/Dbs.  It should be just send any unrelated statements to the db, have them execute, and return a list of results for each.  I have recently been working on KMIP, which almost gets that aspect right.)

What sort of performane improvements did you get for Oracle and Postgresql?

First of all, let me say our banchmark tests are probably not totally accurate. Running Insert, then batch insert does not give the same result than in reverse order. The second test runs always a bit faster, so I did put batchInsert before insert to make sure there is a real gain...

On Postgres running localy (on SSD), I get the follonwing result:

[java] [no Session]  ------ simpleormBatchInsert                    90   (119) (microsecond/row) [Statistics Trans: 12 Flush: 10006 Finddb: 10009 Querydb: 0 CurTim: 1359812349318]
[java] [no Session]  ------ simpleormInsert                         242   (119) (microsecond/row) [Statistics Trans: 13 Flush: 20006 Finddb: 20012 Querydb: 0 CurTim: 1359812351745]

So batching is almost 3 time faster than row by row flush on Postgres. It's even kicker than raw jdbc (well, I expected so).

On Oracle, my tests run on a LAN Virtual Machine running a non optimized Oracle install.

jdbc : ~1500 microsecond/row
simpleormBatchInsert : ~50 microsecond/row (!!)
simpleormInsert : ~2500 microsecond/row

Here the result is really spectacular, as network overhead really counts in this situation.
I also think that batching in Oracle jdbc is much more efficient than with Postgres.
I think that Postgresql network protocol only allows for one parameter set to be sent at once (see  http://www.postgresql.org/docs/9.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY). Some initialisation steps are saved, but sending parameters is done multiple time... So basically with Postgres, PreparedStatement is created once and cached but each parameter set is sent separately (confirmed by running visualvm).

I'm going to make tests on a real life example on monday on a workload that takes about one hour (on Postgres), 60% of the time creating similar new records in the database.

Best regards,
Franck

Adding batch update support in SimpleORM

Hi all,

I just commited a modification that add jdbc batch support in SimpleROM.

By default, everything works as before, so this should have no impact on 
running projects.

But SSessionJdbc now has a method setUseBatchUpdate(boolean useBatch) 
that will change its behaviour: when set to true, flushing will try to 
use batch.

For all subsenquent dirty records in the dataset that result in the same 
sql statement, it will create one only batch with different parameters, 
and execute that batch in one jdbc call.
If the statement changes, it will execute the current batch and create a 
new one.
This will be particularly useful if you want to insert or update a bunch 
of records of the same SRecordMeta, on the same columns. Then the whole 
dataset will be treated in one batch.
The main gain is that the network overhead is minimized when sending the 
jdbc command.

Benchmark shows significant performance improvements, even locally, with 
Hsql (default ant target), Postgresql and Oracle. I could not test other 
databases.

Oh, great, so why not make it the default ?

Well, this has a cost: as all (similar) records are sent in one batch, 
it is much more difficult to find the faulty record in case of a 
SQLException.
For example, if one of your records breaks a constraint in the database, 
you will get a SExecption.Jdbc error, but the getInstance() method will 
return null, and you won't have acces to the faulty record...

The other tradeoff, is that some SGenerator don't play well with this 
approach.
Specifically, those that try to get the generated key after the record 
is inserted, like SGeneratorInsertIdentity.
Then, if useBatchUpdate is set to true, it will gracefully fallback to 
the traditional flush(ri)... Notice that Hibernate behaves the same with 
IDENTITY.
If someone has an idea of a solution to that problem, I still created a 
void postUpdateWithGeneratedKey(SSessionJdbc session, SRecordInstance[] 
dirtyRecords, int[] result).
This method is called after the batch is executed, and has an array of 
all records in the batch... Then, I don't know what to do to get the 
ids... (I don't use IDENTITY at all on my projects).

Finally, I didn't test some databases I don't have access to: MS 
SqlServer for example. There might be some work to do on the method 
SDriverXXX.checkBrokenOptimisticLock, if the driver does not return 1 or 
Statement.SUCCESS_NO_INFO (-2) on success (MS SqlServer might return 
more than one, including records modified by triggers, etc...). That 
said, batchUpdate is off by default, so it does not break anything.

I added a benchmark test. I also ran all the tests with batchUpdate=true 
as default, and nothing broke, so I am pretty confident this works, but 
any feedback is welcome !

Best regards,

Franck

New functionality in SQueryTransient, to retrieve data without grouping

Hi,

I have just added and commited a possibility in SQueryTransient to do:

trsQry.selectField(String tableAlias, SFieldScalar fld);

Of course, the same SQueryTransient cannot mix aggregate functions and 
selectFields.

This allows to create a query using SimpleORM objects and metadata, and 
to retrieve arbitrary fields from the relation without doing any 
aggregate nor creating SRecordInstances.
So you use Query part of our beloved ORM, and not the true ORM.
Much like doing jdbc and getting the ResultSet, but using SRecordMeta, 
SFieldMeta, SQuery joins, getting the SRecordGeneric casting 
capabilities, using the SDriver, etc...

Hope this will be useful to some of you. Any comment will be welcome, of 
course.

Regards,

Franck

Sourceforge SVN repository not working?

Strangely, I can't connect to the sourceforge svn repository at the recommended url

http://simpleorm.svn.sourceforge.net/svnroot/simpleorm/trunk/simpleorm

nor at the "version 2" url 

http://svn.code.sf.net/p/simpleorm/MOUNTPOINT/

Mind you, viewing the code using a web browser at
http://simpleorm.svn.sourceforge.net/viewvc/simpleorm/
seems to work. 

Is sourceforge having problems?  Weird...  Below is the error message

--
John 

Unable to connect to a repository at URL
 'http://simpleorm.svn.sourceforge.net/svnroot/simpleorm/trunk/simpleorm'
OPTIONS of
 'http://simpleorm.svn.sourceforge.net/svnroot/simpleorm/trunk/simpleorm':
 could not connect to server (http://simpleorm.svn.sourceforge.net)

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

Help! License is...

A client has asked me to specify the libraries I use and their licenses, to ensure that I have permission to
release my entire project under Apache V2.

On the SimpleORM website is says "Apache style license".  I can't find any documentation of the actual
license used...  If you haven't decided, then Apache V2 would be the most convenient.

Thanks for your (hopefully prompt) response!

--
John 

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

Hooking into row loading?

Hi all,

is there a way to hook into the row loading process of SimpleORM?
I need to do some processing on the rows before the application sees them.

Regards,
Jo

P.S.: In case anybody is interested: I'm toying with ideas to cope with 
database schema version upgrades. I control the schema and its 
evolution, which helps a lot, but I can't take the application offline 
so I need to do conversions on the fly, i.e. during load.
I'm well aware that I need to handle a few more details than just 
loading, but that's the problem that I currently don't know how to solve 

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

[PATCH] fix for bug in SRecordGeneric [1 Attachment]

<*>[Attachment(s) from Noel Grandin included below]

Hi

There is a bug in SRecordGeneric.getBoolean that manifests in some odd 
situations.

Patch attached.

Regards, Noel Grandin

Disclaimer: http://www.peralex.com/disclaimer.html

<*>Attachment(s) from Noel Grandin:

<*> 1 of 1 File(s)
http://groups.yahoo.com/group/SimpleORM/attachments/folder/266025043/item/list 
  <*> patch.txt

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

[patch] add CLOB datatype [1 Attachment]

<*>[Attachment(s) from Noel Grandin included below]

Hi

The attached file adds the CLOB datatype.

Regards, Noel Grandin

Disclaimer: http://www.peralex.com/disclaimer.html

<*>Attachment(s) from Noel Grandin:

<*> 1 of 1 File(s)
http://groups.yahoo.com/group/SimpleORM/attachments/folder/838970561/item/list 
  <*> SFieldClob.java

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

Re: Long Time No See - from Bartek Muszynski