-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Query efficiency
PostPosted: Mon Oct 02, 2006 10:54 am 
Beginner
Beginner

Joined: Tue Sep 19, 2006 11:26 am
Posts: 33
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.