Hi,
Lucee ORM seems to be making unnecessary DB update calls when ORM session is flushed. Whenever there’s a 1-to-n relationship between two entities, if you load the master entity and call session flush, Lucee ORM wants to set the id in the child entities to null and back to the original id (either the updates are carried out or if the field is non-nullable in the DB, you get an exception).
This happens in all hql queries or entity loads and creates a lot of overhead for a real application, but for a simple case:
Artist.cfc
component persistent="true" {
property name="id" column="ArtistId" fieldtype="id" generator="increment";
property name="firstname" ormtype="string";
property name="lastname" ormtype="string";
}
Art.cfc
component persistent="true" {
property name="id" column="artid" fieldtype="id" generator="increment";
property name="name" ormtype="string";
property name="price" ormtype="double";
property name="artist" fieldtype="many-to-one" cfc="artist";
}
Code to execute (assuming you have at lease one artist and 2 related arts) :
artist = entityLoadByPk('Artist', 1);
WriteDump(artist);
Creates the below log (the updates happen if flushAtRequestEnd is true or you flush the ORM session by ORMGetSession().flush() within the flush code):
Hibernate:
select
artist0_.ArtistId as ArtistId5_0_,
artist0_.firstname as firstname5_0_,
artist0_.lastname as lastname5_0_
from Artist artist0_ where artist0_.ArtistId=?
Hibernate:
select
art0_.ARTISTID as ARTISTID5_1_,
art0_.artid as artid1_,
art0_.artid as artid4_0_,
art0_.name as name4_0_,
art0_.price as price4_0_,
art0_.ARTISTID as ARTISTID4_0_
from Art art0_ where art0_.ARTISTID=?
Hibernate:
update Art set ARTISTID=null where ARTISTID=?
Hibernate:
update Art set ARTISTID=? where artid=?
Hibernate:
update Art set ARTISTID=? where artid=?
As you see the update calls are completely unnecessary as no change has been made.
I’m using Lucee 5.1.3.18 with MS SQL Server and tried this with both Microsofts Driver and JTD Driver. This seems to be a bug but I’d appreciate any ideas as to why this might be happening. In my real world application, this case forces the already loaded entities to be reloaded into session with new selects after the update and it makes Lucee unusable in terms of performance compared to Railo.
Thanks,
Ozgur