Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » dynamic upates(update statements only for modified fields)
dynamic upates [message #1748228] Mon, 21 November 2016 12:19 Go to next message
Racer I. is currently offline Racer I.Friend
Messages: 3
Registered: November 2016
Junior Member
Hi,

We are currently evaluating a switch from a self-implemented persistence framework to JPA.
Part of that process is comparing special features of our framework with what Hibernate, EclipseLink or OpenJPA offer in that area.
I will be searching myself (for example this board) but would also like to ask in case someone can point me directly to the right information.
Also because I will be on vacation the next two weeks. Its Ok if the analysis ist postponed till afterwards, but maybe with this request I can already get some info before then.
Note : We only use Oracle-DBs, so there might be some assumptions below that are oracle-centric

So this question is about smart updates in eclipselink, i.e. updating only modified columns.
How is this handled :

* Batch/bulk updates (each row might have a different set of modified columns)
will there be multiple statements or one with the superset of columns?
individual statements would defeat the batching-purpose, we think
* will the statements be kept as prepared across transactions and reused for the same subsets?
rebuilding and reparsing would be unwanted overhead
* will special consideration be used for partitioned tables?
updating a partitioning field would mean DELETE+INSERT when the partition changes, so reducing the other columns has no effect so use full update
* will columns with constraints (foreign key and check) be treated special?
removing those has bigger impact, than run-of-the-mill fields, since it avoids the check
* will columns with indexes be treated special?
not updating them can skip the full index maintance
apparently oracle can detect if the value doesn't change and will optimize this, but skipping it entirely is still better, we think
* will the mechanism try to apply common sense?
for example if the statement still contains > 66% (configurable) of all columns and the remainder are all run-of-the-mill simply switch to the full update
* will the mechanism try to keep the number of different statements to a mangeable few?
i.e. less then all permutations of all fields, say a maximum of 5, so as not to flood the database statement cache causing excessive reparsing.
requires finding the closest inclusive match.
* if any of these features are not there, have there been analysises of them with the decision not to implement them?
if so what arguments were made against them?
* are there hooks in the framework that would allow one to implement these without having to directly patch core code?
* are there features implemented in this area that were not touched upon in the list above but could guide a comparison?

Thanks in advance
Re: dynamic upates [message #1748314 is a reply to message #1748228] Tue, 22 November 2016 06:12 Go to previous messageGo to next message
Racer I. is currently offline Racer I.Friend
Messages: 3
Registered: November 2016
Junior Member
Hi,

Two changes :

I had posted this question to a hibernate forum two weeks ago, before my vacation. Please ignore that line.
I had found that hibernate has a dynamic-update=true option. I haven't yet found if OpenJPA has such a feature. The questions above assume that there is something like that.

regards
Re: dynamic upates [message #1749176 is a reply to message #1748228] Fri, 02 December 2016 17:18 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1275
Registered: July 2009
Senior Member
Would have been easier if you had numbered your questions. Most answers are likely found in the docs, but all over the place; I'll try and take a quick stab at answering what I can but leave it to you to find more detailed answers in the docs.

* will the statements be kept as prepared across transactions and reused for the same subsets?
Yes. Statements are tied to a connection, but prepared statements are used and can be cached for reuse depending on the connection pooling options.
* will special consideration be used for partitioned tables?
>IDK. See EclipseLinks support for partitioning for details (https://wiki.eclipse.org/EclipseLink/Examples/JPA/Partitioning maybe a place to start)
* will columns with constraints (foreign key and check) be treated special?
>How 'special'? FK Constraint are taking into account when ordering updates/inserts/deletes, but that is all.
* will columns with indexes be treated special?
>how special? IF they haven't changed, they aren't included. They can be used for caching purposes if configured with the @Index annotation.
* will the mechanism try to apply common sense?
> common sense is a loaded term. EclipseLink only sends changed fields, but you can configure this on a class by class basis, completely customizing the SQL used for CRUD. I haven't seen anyone try to switch between partial and all fields dynamically on an update by update basis though, so it might involve trial and error.
* will the mechanism try to keep the number of different statements to a mangeable few?
>No. This is upto your application design and use of EclipseLink's customizations. Each unique query likely leads to a unique statement.
* if any of these features are not there, have there been analysises of them with the decision not to implement them?
if so what arguments were made against them?
> EclipseLInk is based off of TopLink which has been around for decades. Such decision info just isn't going to be available if it isn't in the bug DB. No reasonable feature is ignored, it just might not have traction unless someone is adamant and wants to implement it.
* are there hooks in the framework that would allow one to implement these without having to directly patch core code?
> likely yes. TopLink/EclipseLink has numerous policies, callbacks etc. Feel free to update the core code though and contribute it back, as it is an open source project!
* are there features implemented in this area that were not touched upon in the list above but could guide a comparison?
>IDK
* Batch/bulk updates (each row might have a different set of modified columns)
will there be multiple statements or one with the superset of columns?
> EclipseLInk supports batching update statements and it is being used, I just don't remember off hand how statements are pooled together, and if a different set of fields causes a new batch statement.

I'd urge you to use JPA and then switch your application to try the different JPA providers to find the flavor that works best for your application.

[Updated on: Fri, 02 December 2016 17:26]

Report message to a moderator

Re: dynamic upates [message #1749278 is a reply to message #1749176] Mon, 05 December 2016 07:52 Go to previous message
Racer I. is currently offline Racer I.Friend
Messages: 3
Registered: November 2016
Junior Member
Hi Chris,

Thanks for your information.

I did learn a few more things in between from the docs and answers to the questions in the hibernate/OpenJPA-forums.

One is that EclipseLink and OpenJPA do these dynamic updates by default and you have to do something if you want full/other updates. In hibernate the full updates are the default and dynamic-updates=true switches.

Another is probably general reliance on the statement caching of the driver or J2EE-container and less/no caching in the JPA-framework itself. We mainly cache the statement string and the batch sizes (for fetching or DML). We optimize the sizes over time based on use. This might also become less necessary with the new oracle driver, which has better dynamic memory management for prepared statements.

Yet another is that apparently noone cares about keeping the number of statements low. I've thought about it and concluded we may have been to fussy about this (questions 3 to 9). I guess few applications will generate 20 different updates per table, much less per table in a single process (say turning orders into deliveries). We will probably add some logging to our current implementation to find out how many statements we would get if we relaxed all the extra rules and used one statement for each modified field combination. If the difference is negligible we can switch to any of the JPA-providers without problems.

The main remaining question would be if statements are grouped by modified column set. So if a process produces two different update statements for a given table in an alternating pattern will that result in 1000 1-row batches or will they be reordered to two 500-row batches? Our implementation would always result in one statement (superset of all actually occuring ones) to avoid multiple batches per table, even if that results in more work for the database by updating unmodified columns.

regards,
Previous Topic:Correct usage of Java EntityManager
Next Topic:FetchType.EAGER not always loaded eagerly
Goto Forum:
  


Current Time: Tue Dec 12 16:05:35 GMT 2017

Powered by FUDForum. Page generated in 0.01521 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software