ManyToMany performance Issue

[SomuReddy <ch...@gmail.com>]

Hi All,

I am using the ManyToMany relationships to fetch data from the two tables. 
I suspected that here every time one JDBC prepared statment is executing for
the each row to retrieve from the other table. here is the example

 @ManyToMany
 @JoinTable(name = "X_FWA_DISPATCH_LOGIC", joinColumns = @JoinColumn(name =
"FWA_ID", referencedColumnName = "FWA_ID"), inverseJoinColumns = {
@JoinColumn(name = "DISPATCH_LOGIC_ID", referencedColumnName =
"DISPATCH_LOGIC_ID") })
 private List<DispatchLogicDO> disLogicList;

the prepared statement is like this

1783  fpa_Junit  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn
614868134> executing prepstmnt 907818524 SELECT t0.FWA_ID, t0.FMU_ID,
t0.FPU_ANALYSIS_ID, t0.IDENTIFIER, t0.LAST_MODIFIED FROM FPA2.FWA t0 WHERE
(t0.FPU_ANALYSIS_ID = ?) [params=(int) 302]
1803  fpa_Junit  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn
614868134> [20 ms] spent

So suppose if there is 10 rows in second table it is executing the 10
prepared statements, hence the performence is very less.. 

is there anyway to retrieve the whole list with one SQL Query(means only one
prepared stament)??

or any other way to improve the performence here???

Regards,
Somu



-- 
View this message in context: http://www.nabble.com/ManyToMany-performance-Issue-tp15072275p15072275.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: ManyToMany performance Issue

[Werner Punz <we...@gmail.com>]

SomuReddy schrieb:
> 
> Hi All,
> 
> I am using the ManyToMany relationships to fetch data from the two tables. 
> I suspected that here every time one JDBC prepared statment is executing for
> the each row to retrieve from the other table. here is the example
> 
>  @ManyToMany
>  @JoinTable(name = "X_FWA_DISPATCH_LOGIC", joinColumns = @JoinColumn(name =
> "FWA_ID", referencedColumnName = "FWA_ID"), inverseJoinColumns = {
> @JoinColumn(name = "DISPATCH_LOGIC_ID", referencedColumnName =
> "DISPATCH_LOGIC_ID") })
>  private List<DispatchLogicDO> disLogicList;
> 
> the prepared statement is like this
> 
> 1783  fpa_Junit  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn
> 614868134> executing prepstmnt 907818524 SELECT t0.FWA_ID, t0.FMU_ID,
> t0.FPU_ANALYSIS_ID, t0.IDENTIFIER, t0.LAST_MODIFIED FROM FPA2.FWA t0 WHERE
> (t0.FPU_ANALYSIS_ID = ?) [params=(int) 302]
> 1803  fpa_Junit  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn
> 614868134> [20 ms] spent
> 
> So suppose if there is 10 rows in second table it is executing the 10
> prepared statements, hence the performence is very less.. 
> 



> is there anyway to retrieve the whole list with one SQL Query(means only one
> prepared stament)??
> 

As far as I know ORM mappers this myriad of statements is caused
by lazy loading.
What happens is following: The first statement is issued, a collection
of 10 proxy objects for right hand side of the relationship is added 
(the left hand side is loaded fully), once you access one of them then 
an additional query is issued to retrieve the objects data.

To overcome this you have to adjust your prefetch strategies:
fetch = FetchType.EAGER can overcome this

or you can use fetch joins (10.1.3 in the openjpa manual) to achieve this.