Hi,
I have written an embedded EJB application which extracts data from a series of CSV files and writes them to a MySQL 4.0.27 database.
I have got the following two entity beans:
The Account bean.
Code:
/**
* Account generated by hbm2java
*/
@Entity
@Cache (usage=CacheConcurrencyStrategy.TRANSACTIONAL)
@Table(name = "account", catalog = "ocss", uniqueConstraints = {})
@Name("account")
@NamedQuery(name="FindByHouseAndOrigRef",
query="select a from Account a " +
"where a.originalRef = ?2" +
" and a.title.fulfillmentHouse.id = ?1")
@Roles({@Role(name="selectedAccount", scope=ScopeType.SESSION),
@Role(name="confirmAccount", scope=ScopeType.SESSION)})
@Scope(ScopeType.SESSION)
public class Account implements java.io.Serializable {
// Fields
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private Address deliveryAddress;
private Address invoiceAddress;
private Title title;
private String originalRef;
private String currencySymbol;
private Date startDate;
private Date cancelDate;
private String email;
private Set<UserAccount> userAccounts = new HashSet<UserAccount>(0);
private Set<AccountTx> accountTxes = new HashSet<AccountTx>(0);
private AccountStatus accountStatus;
private AccountStatusSpark accountStatusSpark;
// Constructors
/** default constructor */
public Account() {
}
/** minimal constructor */
public Account(int id, Address deliveryAddress,
Address invoiceAddress, Title title, String originalRef,
String currencySymbol, Date startDate, String email) {
this.id = id;
this.deliveryAddress = deliveryAddress;
this.invoiceAddress = invoiceAddress;
this.title = title;
this.originalRef = originalRef;
this.currencySymbol = currencySymbol;
this.startDate = startDate;
this.email = email;
}
/** full constructor */
public Account(int id, Address deliveryAddress,
Address invoiceAddress, Title title, String originalRef,
String currencySymbol, Date startDate, Date cancelDate,
String email, Set<UserAccount> userAccounts,
Set<AccountTx> accountTxes, AccountStatus accountStatus,
AccountStatusSpark accountStatusSpark) {
this.id = id;
this.deliveryAddress = deliveryAddress;
this.invoiceAddress = invoiceAddress;
this.title = title;
this.originalRef = originalRef;
this.currencySymbol = currencySymbol;
this.startDate = startDate;
this.cancelDate = cancelDate;
this.email = email;
this.userAccounts = userAccounts;
this.accountTxes = accountTxes;
this.accountStatus = accountStatus;
this.accountStatusSpark = accountStatusSpark;
}
// Property accessors
@Id
@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = true)
@GeneratedValue
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
@ManyToOne(cascade = {}, fetch = FetchType.EAGER)
@JoinColumn(name = "delivery_address_id", unique = false, nullable = false, insertable = true, updatable = true)
public Address getDeliveryAddress() {
return this.deliveryAddress;
}
public void setDeliveryAddress(Address deliveryAddress) {
this.deliveryAddress = deliveryAddress;
}
@ManyToOne(cascade = {}, fetch = FetchType.EAGER)
@JoinColumn(name = "invoice_address_id", unique = false, nullable = false, insertable = true, updatable = true)
public Address getInvoiceAddress() {
return this.invoiceAddress;
}
public void setInvoiceAddress(Address invoiceAddress) {
this.invoiceAddress = invoiceAddress;
}
@ManyToOne(cascade = {}, fetch = FetchType.EAGER)
@JoinColumn(name = "title_id", unique = false, nullable = false, insertable = true, updatable = true)
public Title getTitle() {
return this.title;
}
public void setTitle(Title title) {
this.title = title;
}
@Column(name = "original_ref", unique = false, nullable = false, insertable = true, updatable = true, length = 50)
public String getOriginalRef() {
return this.originalRef;
}
public void setOriginalRef(String originalRef) {
this.originalRef = originalRef;
}
@Column(name = "currency_symbol", unique = false, nullable = false, insertable = true, updatable = true, length = 50)
public String getCurrencySymbol() {
return this.currencySymbol;
}
public void setCurrencySymbol(String currencySymbol) {
this.currencySymbol = currencySymbol;
}
@Column(name = "start_date", unique = false, nullable = false, insertable = true, updatable = true, length = 19)
public Date getStartDate() {
return this.startDate;
}
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
@Column(name = "cancel_date", unique = false, nullable = true, insertable = true, updatable = true, length = 19)
public Date getCancelDate() {
return this.cancelDate;
}
public void setCancelDate(Date cancelDate) {
this.cancelDate = cancelDate;
}
@Column(name = "email", unique = false, nullable = false, insertable = true, updatable = true, length = 100)
public String getEmail() {
return this.email;
}
public void setEmail(String email) {
this.email = email;
}
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "account")
public Set<UserAccount> getUserAccounts() {
return this.userAccounts;
}
public void setUserAccounts(Set<UserAccount> userAccounts) {
this.userAccounts = userAccounts;
}
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "account")
public Set<AccountTx> getAccountTxes() {
return this.accountTxes;
}
public void setAccountTxes(Set<AccountTx> accountTxes) {
this.accountTxes = accountTxes;
}
@OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "account")
public AccountStatus getAccountStatus() {
return this.accountStatus;
}
public void setAccountStatus(AccountStatus accountStatus) {
this.accountStatus = accountStatus;
}
@OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "account")
public AccountStatusSpark getAccountStatusSpark() {
return this.accountStatusSpark;
}
public void setAccountStatusSpark(AccountStatusSpark accountStatusSpark) {
this.accountStatusSpark = accountStatusSpark;
}
}
The transaction bean.Code:
/**
* AccountTx generated by hbm2java
*/
@Entity
@Table(name = "account_tx", catalog = "ocss", uniqueConstraints = {})
public class AccountTx implements java.io.Serializable
{
// Fields
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private Account account;
private AccountTxType accountTxType;
private Date txDate;
private String description;
private BigDecimal value;
private BigDecimal balance;
private String currencySymbol;
private Set<AccountTxIssue> accountTxIssues = new HashSet<AccountTxIssue>(0);
// Constructors
/** default constructor */
public AccountTx()
{
}
/** minimal constructor */
public AccountTx(int id, Account account, AccountTxType accountTxType,
Date txDate, String description, BigDecimal value,
BigDecimal balance, String currencySymbol)
{
this.id = id;
this.account = account;
this.accountTxType = accountTxType;
this.txDate = txDate;
this.description = description;
this.value = value;
this.balance = balance;
this.currencySymbol = currencySymbol;
}
/** full constructor */
public AccountTx(int id, Account account, AccountTxType accountTxType,
Date txDate, String description, BigDecimal value,
BigDecimal balance, String currencySymbol,
Set<AccountTxIssue> accountTxIssues)
{
this.id = id;
this.account = account;
this.accountTxType = accountTxType;
this.txDate = txDate;
this.description = description;
this.value = value;
this.balance = balance;
this.currencySymbol = currencySymbol;
this.accountTxIssues = accountTxIssues;
}
// Property accessors
@Id
@GeneratedValue
@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = true)
public int getId()
{
return this.id;
}
public void setId(int id)
{
this.id = id;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "account_id", unique = false, nullable = false, insertable = true, updatable = true)
public Account getAccount()
{
return this.account;
}
public void setAccount(Account account)
{
this.account = account;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "account_tx_type_id", unique = false, nullable = false, insertable = true, updatable = true)
public AccountTxType getAccountTxType()
{
return this.accountTxType;
}
public void setAccountTxType(AccountTxType accountTxType)
{
this.accountTxType = accountTxType;
}
@Column(name = "tx_date", unique = false, nullable = false, insertable = true, updatable = true, length = 19)
public Date getTxDate()
{
return this.txDate;
}
public void setTxDate(Date txDate)
{
this.txDate = txDate;
}
@Column(name = "description", unique = false, nullable = false, insertable = true, updatable = true, length = 100)
public String getDescription()
{
return this.description;
}
public void setDescription(String description)
{
this.description = description;
}
@Column(name = "value", unique = false, nullable = false, insertable = true, updatable = true, precision = 18, scale = 5)
public BigDecimal getValue()
{
return this.value;
}
public void setValue(BigDecimal value)
{
this.value = value;
}
@Column(name = "balance", unique = false, nullable = false, insertable = true, updatable = true, precision = 18, scale = 5)
public BigDecimal getBalance()
{
return this.balance;
}
public void setBalance(BigDecimal balance)
{
this.balance = balance;
}
@Column(name = "currency_symbol", unique = false, nullable = false, insertable = true, updatable = true, length = 50)
public String getCurrencySymbol()
{
return this.currencySymbol;
}
public void setCurrencySymbol(String currencySymbol)
{
this.currencySymbol = currencySymbol;
}
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "accountTx")
public Set<AccountTxIssue> getAccountTxIssues()
{
return this.accountTxIssues;
}
public void setAccountTxIssues(Set<AccountTxIssue> accountTxIssues)
{
this.accountTxIssues = accountTxIssues;
}
}
I am using the FindByHouseAndOrigRef named query to read an account to populate the account field of the transaction bean and then writing the transaction to the database. The problem is that I need to go through the process of reading an account and writing away a transaction somwhere in the region of 7 million times but the FindByHouseAndOrigRef query is having a significant effect on performance (Running the code through a profiler shows that it is taking ~5 secs to run 1000 times - scaling this up is unacceptably long).
We have tried a number of things to try to reduce the time spent running the query. Adding indexes to the account table helped a little but it is still taking ~5 secs to run 1000 times. You may notice that the title and two address fields have their fetch type set to eager (Part of the web front end is making use of this) changing these to lazy temporarily also had a negligable effect - <5%.
During our investigation we found that if we run a similar query that returns just the account id rather than all the fields of the account bean, it runs significantly quicker - somewhere in the region of 75% quicker in fact! Is there any way we can utilise this possibly by adding some annotations to the account bean? If I try to update the transaction bean with an account bean with just the id, the framework currently attempts to blank out the other fields for that account.
Thanks.
Simon