Eclipse Community Forums: EclipseLink » Slow Query Extraction

Help

Home

Home » Eclipse Projects » EclipseLink » Slow Query Extraction

Show: Today's Messages :: Show Polls :: Message Navigator

Slow Query Extraction [message #1060194]

Thu, 23 May 2013 13:05

pdvmipv Mising name

Messages: 12
Registered: October 2010

Junior Member

I have an Oracle database and i use EclipseLink, this is an extract of my persistence classes:

@

Entity
@Table(name = "TACOS")
	
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_TACOS")
@SequenceGenerator(name = "SEQ_TACOS", sequenceName = "SEQ_TACOS", allocationSize = 1, initialValue = 1)
@Basic(optional = false)
@Column(name = "ID_TACOS")
private Long idTacos;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "tacos")
private List<PstdCommon> pstdCommonList;

[b]public List<PstdCommon> getPstdCommonList() {
        return pstdCommonList;
    }[/b]

@Entity
@Table(name = "PSTD_COMMON")
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_PSTD_TACOS")
@SequenceGenerator(name = "SEQ_PSTD_TACOS", sequenceName = "SEQ_PSTD_TACOS", allocationSize = 1, initialValue = 1)
@Basic(optional = false)
@Column(name = "ID_PSTD_TACOS")

@JoinColumn(name = "ID_TACOS", referencedColumnName = "ID_TACOS")
@ManyToOne(optional = false)
private Tacos tacos;
	
@OneToOne(cascade = CascadeType.ALL, mappedBy = "pstdCommon")
private PstdPcmAnalog pstdPcmAnalog;

@Entity
@Table(name = "PSTD_PCM_ANALOG")
@JoinColumn(name = "ID_PSTD_TACOS", referencedColumnName = "ID_PSTD_TACOS", insertable = false, updatable = false)
@OneToOne(optional = false)
private PstdCommon pstdCommon;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "pstdPcmAnalog")
private List<PstdPcmGrid> pstdPcmGridList;

@Entity
@Table(name = "PSTD_PCM_GRID")

@EmbeddedId
protected PstdPcmGridPK pstdPcmGridPK;

@JoinColumn(name = "ID_PSTD_TACOS", referencedColumnName = "ID_PSTD_TACOS", insertable = false, updatable = false)
@ManyToOne(optional = false)
private PstdPcmAnalog pstdPcmAnalog;

@JoinColumn(name = "ID_GRID_TACOS", referencedColumnName = "ID_GRID_TACOS", insertable = false, updatable = false)
@ManyToOne(optional = false)
private GridTacosPcm gridTacosPcm;

NAMED QUERY
-----------
@NamedQuery(name = "Tacos.findTacosByName", query = "SELECT t FROM Tacos t WHERE t.idHeli = :idHeli AND t.idCodHeli = :idCodHeli AND "
+ "t.tacosName = :tacosName")

As a first step i run the '@NamedQuery Tacos.findTacosByName' to get the main object of db (Tacos), from this object i take the list parameters associated with the main object with the function 'getpstdcommonlist()' inside the class main class.
When i do the first access of the data of the list returned by the function 'getpstdcommonlist()'automatically eclipselink performs 30000 query for "fill" all the objects. The execution time on a list of 10000 records is about 48 seconds.

This a part of quey executed:
T-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CONNECTION_NAME, FIELD_LENGHT, HERTZ_REPETITION, MSB, PARAMTER_NAME, PHSYCAL_TYPE, POSITION, PROV_CONV_TYPE FROM PSTD_FULL_FIELD WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.088--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CAL_IMPULSE, CAL_SHORT, CAL_TYPE, CAL_ZERO, DATA_FORMAT, DEPURATION, ENDURANCE, EXTRA_BITS, PRE_F_IMPULSE, PRE_F_SHORT, PRE_F_ZERO FROM PSTD_PCM_ANALOG WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.089--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CHANNEL_FM, COD_FREQ_ACQ, NUM_CYCLE FROM PSTD_ANALOG WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.09--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, LINE_NAME, PROC_CONV, RATE, SIGN, START_BIT, STOP_BIT FROM PSTD_PCM_BUS WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.095--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CONNECTION_NAME, FIELD_LENGHT, HERTZ_REPETITION, MSB, PARAMTER_NAME, PHSYCAL_TYPE, POSITION, PROV_CONV_TYPE FROM PSTD_FULL_FIELD WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.098--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CAL_IMPULSE, CAL_SHORT, CAL_TYPE, CAL_ZERO, DATA_FORMAT, DEPURATION, ENDURANCE, EXTRA_BITS, PRE_F_IMPULSE, PRE_F_SHORT, PRE_F_ZERO FROM PSTD_PCM_ANALOG WHERE (ID_PSTD_TACOS = ?)
[EL Fine]: 2013-05-23 11:06:06.099--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT ID_PSTD_TACOS, CHANNEL_FM, COD_FREQ_ACQ, NUM_CYCLE FROM PSTD_ANALOG WHERE (ID_PSTD_TACOS = ?)
....
[EL Finest]: 2013-05-23 11:07:32.266--ServerSession(1576330609)--Thread(Thread[AWT-EventQueue-0,6,main])--Execute query ReadAllQuery(name="file:/E:/NetBeansProjects/Codice/java/Prove/JTestOracleTacos/build/classes/_JTestOracleTacosPU_url=jdbc:oracle:thin:@172.23.110.159:3159/PJPANDA_user=JPANDA" referenceClass=PstdPcmGrid sql="SELECT DELAY, MINOR_FRAME, WORD_POS, WORD_STEP, ID_PSTD_TACOS, ID_GRID_TACOS FROM PSTD_PCM_GRID WHERE (ID_PSTD_TACOS = ?)")
[EL Fine]: 2013-05-23 11:07:32.267--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT DELAY, MINOR_FRAME, WORD_POS, WORD_STEP, ID_PSTD_TACOS, ID_GRID_TACOS FROM PSTD_PCM_GRID WHERE (ID_PSTD_TACOS = ?)
[EL Finest]: 2013-05-23 11:07:32.271--ServerSession(1576330609)--Thread(Thread[AWT-EventQueue-0,6,main])--Execute query ReadAllQuery(name="file:/E:/NetBeansProjects/Codice/java/Prove/JTestOracleTacos/build/classes/_JTestOracleTacosPU_url=jdbc:oracle:thin:@172.23.110.159:3159/PJPANDA_user=JPANDA" referenceClass=PstdPcmGrid sql="SELECT DELAY, MINOR_FRAME, WORD_POS, WORD_STEP, ID_PSTD_TACOS, ID_GRID_TACOS FROM PSTD_PCM_GRID WHERE (ID_PSTD_TACOS = ?)")
[EL Fine]: 2013-05-23 11:07:32.273--ServerSession(1576330609)--Connection(380449691)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT DELAY, MINOR_FRAME, WORD_POS, WORD_STEP, ID_PSTD_TACOS, ID_GRID_TACOS FROM PSTD_PCM_GRID WHERE (ID_PSTD_TACOS = ?)
[EL Finest]: 2013-05-23 11:07:32.28--ServerSession(1576330609)--Thread(Thread[AWT-EventQueue-0,6,main])--Execute query ReadAllQuery(name="file:/E:/NetBeansProjects/Codice/java/Prove/JTestOracleTacos/build/classes/_JTestOracleTacosPU_url=jdbc:oracle:thin:@172.23.110.159:3159/PJPANDA_user=JPANDA" referenceClass=PstdPcmGrid sql="SELECT DELAY, MINOR_FRAME, WORD_POS, WORD_STEP, ID_PSTD_TACOS, ID_GRID_TACOS FROM PSTD_PCM_GRID WHERE (ID_PSTD_TACOS = ?)")
[
....

On the other hand, if I create a query with INNER JOIN, between the three tables, which I will return the same number of data, the execution time is a little more than 1 second.

The query is:

"SELECT PSTD_COMMON.*, PSTD_PCM_ANALOG.*, PSTD_PCM_GRID.* FROM PSTD_COMMON INNER JOIN PSTD_PCM_ANALOG ON (PSTD_COMMON.ID_PSTD_TACOS = PSTD_PCM_ANALOG.ID_PSTD_TACOS) INNER JOIN PSTD_PCM_GRID ON (PSTD_COMMON.ID_PSTD_TACOS = PSTD_PCM_GRID.ID_PSTD_TACOS) WHERE PSTD_COMMON.ID_TACOS = ? AND PSTD_PCM_GRID.ID_GRID_TACOS = ?"

There is a way to work with eclispelink without executed multipe query, and use a simple inner join like the native query and fill the persistence classes?
It's normal that eclipselink use so many query to extract the data?

Report message to a moderator

Re: Slow Query Extraction [message #1060810 is a reply to message #1060194]

Tue, 28 May 2013 13:33

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

You can use a join fetch or a batch fetch, either through the annotations @JoinFetch, @BatchFetch, or query hints "eclipselink.join-fetch", "eclipselink.batch", or use join fetch in JPQL.

See,
http://java-persistence-performance.blogspot.com/2010/08/batch-fetching-optimizing-object-graph.html

James : Wiki : Book : Blog : Twitter

Report message to a moderator