Hi,

I use a list to access array elements from an ArrayList. I am running some overnight tests and it seems everything runs fine in the beginning and the list is returned in under 1 second. However, after several hours it starts taking about 5 seconds or longer to return the same list. Please note that the data that is being retrieved already exists in the database before any testing starts. I am not sure how to solve this issue and any help (or hints) would be greatly appreciated.

Note: this was originally posted where a transient list was used. However, tests were run using using non-transient list (getLines) and the problem still occurs so the problem is not isolated to transient lists.

Regards,
Rick.

SAPDB 7.4.03.31 32 bit
Hibernate 3.1
JBoss 3.2.6

public class Order {
private long id;
private List<OrderLine> lines = new ArrayList<OrderLine>();

@Id(generate = GeneratorType.AUTO)
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "order_header_id")
@OrderBy("pickSequence, targetColorIndex")
public List<OrderLine> getLines() {
return lines;
}
public void setLines(List<OrderLine> lines) {
this.lines = lines;
}

...

}

I did some further investigation and it seems that the problem occurs because each order contains 80 lines. When I reduced the number of lines to 25 it never took longer than 1 second to return the list.

This problem seems to be related to the cache size.

After considerably more testing I traced the problem down to the SQL query used by hibernate for the OneToMany join. It seems that SAPDB will execute this query effeciently for about 2 hours without an index when there are many lines to join (more than 80). After that the query seems to take considerably longer (perhaps an internal DB cache gets full). This was fixed by adding an index for the join column to the table (using create index SQL). How can the same index be added to the table using annotations?