gmane.comp.java.orm.simpleorm

Re: Insert/select...

anthony berglas — 2013-03-05T23:51:35

Hello Franck,

I think that the important thing is that it be practically useful.  So what
you have done is good.  Update and Delete should follow the same pattern,
so it might be nice to add them while you are at it, but not important.
 Not sure about name.

OR really requires parenthesis, and they are messy.  There are two
approaches, one is to add .leftParen(), .rightParen() etc. and the other is
to do it properly building up an expression tree of objects.  The trouble
with the latter is it greatly complicates the simple, common case.
 Non-equijoin references would take it to a whole new and rather scary
level.

Incidentally, I think that even Oracle now supports the Datalog style
recursive querys (WITH ... UNION).  So almost a real standard.

Regards,

Anthony



On Tue, Mar 5, 2013 at 6:29 PM, Franck Routier <franck.routier< at >axege.com>wrote:

Re: Insert/select...

Franck Routier — 2013-03-05T08:29:05

Hi Anthony,

I am very conscious this is the very limit of what an ORM should do. And 
I know we want to keep SimpleORM simple :-)
But including the feature into the library made it possible to achieve 
it with very few lines of code...

Another possibility would be to factor out all the query generation part 
of SimpleORM into a separate package, but I didn't want to break everything.
I also still have in mind that if we take that direction, we will want 
to include some missing functionalities, like more complex query 
conditions :
- OR
- parenthesis
- maybe SFieldReference based on non equi-join
The latter can be very convinient when using nested-sets based 
hierarchies (http://en.wikipedia.org/wiki/Nested_set_model) to avoid 
recursivity and handle hierarchies in sql without relying on proprietary 
extensions like Oracle's connect by. Currently I use SQuery.rawJoin to 
handle this kind of hierarchies, which we use a lot in our app...

That said, I realize I've been a bit shortsighted, as I just didn't 
t

Re: Insert/select...

anthony berglas — 2013-03-04T23:13:32

Cool.  Good that you called it raw.  I suppose multi-row updates and
deletes would be next on this agenda.

I had intended to leave that out of SimpleOrm as you know, but I can see it
is nice to use the OO fields to build the queries.

Anthony

On Mon, Mar 4, 2013 at 8:43 PM, Franck Routier <franck.routier< at >axege.com>wrote:

Insert/select...

Franck Routier — 2013-03-04T10:43:39

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)

Re: SDriverOracle OffesetStrategy

Franck Routier — 2013-02-08T11:28:54

Hi Anthony,

no, I never had any single incident in production with Postgresql 
(approx. 20 real production servers + internal databases).
The only point is to have a well tuned routine vacuum task (see 
http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html).

Regards,

Franck

Le 07/02/2013 05:58, anthony berglas a écrit :

Re: SDriverOracle OffesetStrategy

anthony berglas — 2013-02-07T04:58:01

Have you ever had an issue with PostgreSql in production?  I recently used
it to build a text index, and it seemed quite good, although slightly
slower than MS SQL on several queries.

One trick I like about PostgreSql is that it barely buffers db blocks in
memory itself.  Instead it just relies on the O/S to buffer the files.
 Simple and fast.

Anthony

On Thu, Feb 7, 2013 at 2:17 AM, Franck Routier <franck.routier< at >axege.com>wrote:

Re: SDriverOracle OffesetStrategy

Franck Routier — 2013-02-06T16:17:51

Hi,

Le 05/02/2013 00:40, anthony berglas a écrit :
Well, I just discovered another gotcha with this 'rownum' approach.

In this end, we embed a query within one (limit) or two (limit/offset) 
layers of Oracle specific query like this:

select realquery.* from
(select .... from ....) realquery
where rownum < limit

This is well, but... Oracle puts another gotcha here.

In Postgresql, you can do this :

select * from (
select id, id from mytable) realquery;

works fine.

Doing the same in Oracle will result in 00918. 00000 -  "column 
ambiguously defined".
You cannot select * from a subquery that as several columns named or 
aliased the same...
This won't probably hurt anyone in Simpleorm, except if using a 
rawQuery, with several columns named the same, and with limit/offset, 
under Oracle.

Well, guess what: we do that in our project... I will change this, as 
this has no real interest, but it was an automatically generated query, 
and it was convinient not to bother.

I really prefer Postgresql in many wa

Re: SDriverOracle OffesetStrategy

anthony berglas — 2013-02-04T23:40:39

Again well done.

Again, it amazes me after all these years that Oracle does not support
these facilities more easily.  Why put up a barrier for people to migrate
to Oracle!

Anthony

On Tue, Feb 5, 2013 at 4:01 AM, Franck Routier <franck.routier< at >axege.com>wrote:

Re: SDriverOracle OffesetStrategy

Franck Routier — 2013-02-04T18:01:44

Hi,

this has been a long process... but I finally implemented a QUERY 
OffsetStrategy for SDriverOracle that uses Oracle's native rownum idiom.
I had to slightly modify the signature of the limitSQL() method, but 
this was worth the effort:
I have a real life example where I read 1 million rows with a 15000 
limit size, do some deduplication, and insert into another table.
Before this modification, it took 54 minutes on Postgresql (on a 
slightly slower server), but 2 hours and 25 minutes on Oracle (and even 
2h38 without batch updates)...
Query time was getting longer and longer as the offset increased...

After the modification, Oracle takes only 43 minutes !

Comments are welcome,

Franck


Le 03/10/2010 02:20, berglas< at >SpreadsheetDetective.com a écrit :

Re: Adding batch update support in SimpleORM

Franck Routier — 2013-02-04T14:17:22

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 :

Re: Adding batch update support in SimpleORM

Noel Grandin — 2013-02-04T14:05:36

On 2013-02-01 18:22, Franck Routier wrote:
Cool!

Good, because not all JDBC drivers support batching (e.g. H2).

Thanks, Noel Grandin


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




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

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: Adding batch update support in SimpleORM

Franck Routier — 2013-02-04T09:01:21

Benchmark test on a distant (LAN) Postgresql server :

[java] [no Session]  ------ simpleormBatchInsert                    
190   (656) (microsecond/row) [Statistics Trans: 12 Flush: 10006 Finddb: 
10009 Querydb: 0 CurTim: 1359968258612]
[java] [no Session]  ------ simpleormInsert                         
891   (656) (microsecond/row) [Statistics Trans: 13 Flush: 20006 Finddb: 
20012 Querydb: 0 CurTim: 1359968267526]

So this confirms that batching brings some performance improvement, but 
that it is not as noticeable as it seems to be with Oracle... More to 
come with a real life test on Oracle...

Franck


Le 02/02/2013 15:21, Franck Routier a écrit :

Re: Adding batch update support in SimpleORM

Franck Routier — 2013-02-02T14:21:52

Hi Anthony,

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

Thanks :-)
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 : ~

Re: Adding batch update support in SimpleORM

anthony berglas — 2013-02-02T00:42:09

Excellent work.

(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?

Anthony

On Sat, Feb 2, 2013 at 2:22 AM, Franck Routier <franck.routier< at >axege.com>wrote:

Adding batch update support in SimpleORM

Franck Routier — 2013-02-01T16:22:21

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

New functionality in SQueryTransient, to retrieve data without grouping

Franck Routier — 2013-01-30T17:57:11

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

Re: Sourceforge SVN repository not working?

anthony berglas — 2013-01-23T02:39:06

First Works for me.  Try again, maybe source forge was down.

Anthony

On Tue, Jan 22, 2013 at 9:48 AM, <jea< at >hbaspecto.com> wrote:

Sourceforge SVN repository not working?

2013-01-21T23:48:08

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 req

Re: Re: Help! License is...

anthony berglas — 2013-01-03T00:55:12

Hello John,

Sounds like you have a really interesting project.  I had always thought of
SimpleOrm as mainly for CRUDy applications with lots of single row updates,
it is interesting that you are using it more for its data sets.  One thing
that I would like to add to SimpleOrm is to be able to output a Data Set as
CSV.  That should be really easy to do.

Thanks for the info.

Anthony

On Thu, Jan 3, 2013 at 10:07 AM, <jea< at >hbaspecto.com> wrote:

Re: Help! License is...

2013-01-03T00:07:30

Thanks Anthony.

I see the license requires me to tell you that we are using SimpleORM in the PECAS software for spatial economic modelling and forecasting.  PECAS is used by public agencies for long-range planning of land use and transportation, in particular land use regulations and transportation infrastructure and services provision.

http://www.hbaspecto.com/pecas/

One of the two main PECAS modules is the Space Development (SD) module, which annually considers each bit of land (legal parcel or grid cell) and uses a monte-carlo simulation (ie. random number generator with probability functions) to select developer actions for each bit of land.  Developers are more likely to build specific types of buildings on specific bits of land that 1) are in desireable locations (i.e. can be rented out at a good price), 2) can be constructed cheaply, and 3) are allowed (or not discouraged) by the regulations.

 
SD uses three threads, one thread grabs groups of parcels and puts them in a SimpleORM dataset.  Another

Re: Help! License is... [1 Attachment]

anthony berglas — 2012-12-20T23:37:31

Hello John,

I have attached the Copyright notice from the source.

I'd be interested to know something about the project you use SimpleOrm
for, and how you use it, and any general feedback.

Anthony

On Fri, Dec 21, 2012 at 8:35 AM, <jea< at >hbaspecto.com> wrote: