20 seconds to load 30k rows from Hibernate, Is it slow?

Question

I use Spring + hibernate make queries into my DB. DB size is around 101k rows.

I do the following specification

Predicate predicate = cb.notEqual(root.get("trscnStatus").as(String.class), 
TradeTransactionStatus.DELETED.name());

predicate = cb.and(predicate, cb.equal(root.get("portfolioName"), 
searchCriteria.getPortfolioName()));

and I do

transactionRepository.findAll(transactionsSearchCriteria, sort);

where is transactionsSearchCriteria my specification with Predicate i mentioned above!

My domain model is next:

@Entity(name="TradeTransactional")
@Table(name="TRANSACTIONS")
public class TradeTransaction implements Serializable, Cloneable{

/**
 * 
 */
private static final long serialVersionUID = 1L;

/**
 * The id.
 */
@Id
@GeneratedValue
private long id;

/**
 * The description.
 */
private String description = "";

/**
 * The comments.
 */
private String comments = "";

/**
 * The mv inv ccy.
 */
@Column(name="mv_inv_ccy")
private double mvInvCcy;

/**
 * The mv inv ccy eur.
 */
@Column(name="mv_inv_ccy_eur")
private double mvInvCcyEur;

/**
 * The mv inv ccy usd.
 */
@Column(name="mv_inv_ccy_usd")
private double mvInvCcyUsd;

/**
 * The portfolio name.
 */
@Column(name="portfolio_name")
private String portfolioName = "";

/**
 * The bbticker.
 */
@Column(name="ticker")
private String ticker = "";

/**
 * The pershing ref.
 */
@Column(name="brg_ref")
private String portfolioRef = "";

/**
 * The trade ref.
 */
@Column(name="trade_ref")
private String uniqueRef = "";

/**
 * The b2b ref.
 */
@Column(name="b2b_ref")
private String b2bRef = "";

/**
 * The sedol ticker.
 */
@Column(name="sedol_ticker")
private String sedolTicker = "";

/**
 * The security number.
 */
@Column(name="id_isin")
private String securityNumber="";

/**
 * Aggregate
 */
@Column(name="aggregate")
private boolean aggregate = false;

/**
 * The purchase date.
 */
@Column(name="purchase_date")
private Date purchaseDate;

/**
 * The settl date.
 */
@Column(name="settl_date")
private Date settlDate;

@Column(name="effect_date")
private Date effectDate;

private Date timestamp = new Date();

@Column(name="conf_timestamp")
private Date conftimestamp = new Date();

/**
 * The purchase price.
 */
@Column(name="purchase_price")
private double purchasePrice;

/**
 * The number of shares.
 */
@Column(name="number_of_shares")
private double numberOfShares;

/**
 * The number of shares nominal
 */ 
@Column(name="number_of_shares_nominal")
private double numberOfSharesNominal=0;

/**
 * The pdf confirmation link.
 */
@Column(name="pdf_confirmation_link")
private String confirmationFileName;

/**
 * The reference number.
 */
@Column(name="reference_number")
private String referenceNumber;

@Transient
private double contractSize = 1;

@Transient
private String globalId;

/**
 * The name.
 */
private String name = "";

/**
 * The ccy.
 */
@Column(name="crncy")
private String ccy = "";

/**
 * The ccy settl.
 */
@Column(name="crncy_settl")
private String ccySettl = "";

/**
 * The custody.
 */
private String custody="";

/**
 * The exch code.
 */
@Column(name="exch_code")
private String exchCode="";

/**
 * The country of risk.
 */
@Column(name="cntry_of_risk")
private String countryOfRisk="";

/**
 * The stl sts.
 */
@Column(name="stl_sts")
private String stlSts;

/**
 * The stl amt base.
 */
@Column(name="stl_amt_base")
private double stlAmtBase;

private double accrued;

@Column(name="reconciled")
private boolean reconciled = false;

@Column(name="cntrprty")
private String counterpartyName = "";

@Enumerated(EnumType.STRING)
@Column(name="trscn_status")
private TradeTransactionStatus trscnStatus = TradeTransactionStatus.ACTIVE;

@Column(name="cash_account")
private String cashAccount;

@Column(name="counterparty")
private String counterpartyAccount="";

@Enumerated(EnumType.STRING)
@Column(name="trade_type")
private TradeTransactionType tradeType = TradeTransactionType.UNKNOWN;

@Column(name="allocated")
private boolean allocated = true;

@Column(name="market_sector_des")
private String marketSectorDes = "";

@Column(name = "trader_uuid")
private String traderUUID;

/* (non-Javadoc)
     * @see com.meritservus.domain.TradeTransaction#getDescription()
     */
public String getDescription() {
    return description;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getId()
 */
public long getId() {
    return id;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getMvInvCcy()
 */
public double getMvInvCcy() {
    return mvInvCcy;
}

/**
 * Sets the id.
 * 
 * @param id
 *            the new id
 */
public void setId(long id) {
    this.id = id;
}

/**
 * Sets the description.
 * 
 * @param description
 *            the new description
 */
public void setDescription(String description) {
    this.description = description;
}

/**
 * Sets the mv inv ccy.
 * 
 * @param mvInvCcy
 *            the new mv inv ccy
 */
public void setMvInvCcy(double mvInvCcy) {
    this.mvInvCcy = mvInvCcy;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getPortfolioName()
 */
public String getPortfolioName() {
    return portfolioName;
}

/**
 * Sets the portfolio name.
 * 
 * @param portfolioName
 *            the new portfolio name
 */
public void setPortfolioName(String portfolioName) {
    this.portfolioName = portfolioName;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getBbticker()
 */
public String getTicker() {
    return ticker;
}

/**
 * Sets the bbticker.
 * 
 * @param bbticker
 *            the new bbticker
 */
public void setTicker(String ticker) {
    this.ticker = ticker;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getAggregate()
 */
public boolean getAggregate() {
    return aggregate;
}

/**
 * Sets the index.
 * 
 * @param index
 *            the new aggregate
 */
public void setAggregate(boolean aggregate) {
    this.aggregate = aggregate;
}

/**
 * @return the allocated
 */
public boolean getAllocated() {
    return allocated;
}

/**
 * @param allocated the allocated to set
 */
public void setAllocated(boolean allocated) {
    this.allocated = allocated;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getName()
 */
public String getName() {
    return name;
}

/**
 * Sets the name.
 * 
 * @param name
 *            the new name
 */
public void setName(String name) {
    this.name = name;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getCcy()
 */
public String getCcy() {
    return ccy;
}

/**
 * Sets the ccy.
 * 
 * @param ccy
 *            the new ccy
 */
public void setCcy(String ccy) {
    this.ccy = ccy;
}

/* (non-Javadoc)
 * @see java.lang.Object#toString()
 */
@Override
public String toString() {
    return ToStringBuilder.reflectionToString(this);
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getMvInvCcyEur()
 */
public double getMvInvCcyEur() {
    return mvInvCcyEur;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getMvInvCcyUsd()
 */
public double getMvInvCcyUsd() {
    return mvInvCcyUsd;
}

/**
 * Sets the mv inv ccy eur.
 * 
 * @param mvInvCcyEur
 *            the new mv inv ccy eur
 */
public void setMvInvCcyEur(double mvInvCcyEur) {
    this.mvInvCcyEur = mvInvCcyEur;
}

/**
 * Sets the mv inv ccy usd.
 * 
 * @param mvInvCcyUsd
 *            the new mv inv ccy usd
 */
public void setMvInvCcyUsd(double mvInvCcyUsd) {
    this.mvInvCcyUsd = mvInvCcyUsd;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getSecurityNumber()
 */
public String getSecurityNumber() {
    return securityNumber;
}

/**
 * Sets the security number.
 * 
 * @param securityNumber
 *            the new security number
 */
public void setSecurityNumber(String securityNumber) {
    this.securityNumber = securityNumber;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getPershingRef()
 */
public String getPortfolioRef() {
    return portfolioRef;
}

/**
 * Sets the pershing ref.
 * 
 * @param pershingRef
 *            the new pershing ref
 */
public void setPortfolioRef(String portfolioRef) {
    this.portfolioRef = portfolioRef;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getSedolTicker()
 */
public String getSedolTicker() {
    return sedolTicker;
}

/**
 * Sets the sedol ticker.
 * 
 * @param sedolTicker
 *            the new sedol ticker
 */
public void setSedolTicker(String sedolTicker) {
    this.sedolTicker = sedolTicker;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getPurchaseDate()
 */
public Date getPurchaseDate() {
    return purchaseDate;
}

/**
 * Sets the purchase date.
 * 
 * @param purchaseDate
 *            the new purchase date
 */
public void setPurchaseDate(Date purchaseDate) {
    this.purchaseDate = purchaseDate;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getNumberOfShares()
 */
public double getNumberOfShares() {
    return numberOfShares;
}

/**
 * Sets the number of shares.
 * 
 * @param numberOfShares
 *            the new number of shares
 */
public void setNumberOfShares(double numberOfShares) {
    this.numberOfShares = numberOfShares;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getPurchasePrice()
 */
public double getPurchasePrice() {
    return purchasePrice;
}

/**
 * Sets the purchase price.
 * 
 * @param purchasePrice
 *            the new purchase price
 */
public void setPurchasePrice(double purchasePrice) {
    this.purchasePrice = purchasePrice;
}

/**
 * Sets the string purhcase date to date.
 * 
 * @param purchaseDate
 *            the purchase date
 * @param parsePatterns
 *            the parse patterns
 * @throws Exception
 *             the exception
 */
@Transient
public void setStringPurhcaseDateToDate(String purchaseDate, String... parsePatterns) throws Exception{ 
    Date date = DateUtils.parseDate(purchaseDate, parsePatterns);
    setPurchaseDate(date);
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getCustody()
 */
public String getCustody() {
    return custody;
}

/**
 * Sets the custody.
 * 
 * @param custody
 *            the new custody
 */
public void setCustody(String custody) {
    this.custody = custody;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getExchCode()
 */
public String getExchCode() {
    return exchCode;
}

/**
 * Sets the exch code.
 * 
 * @param exchCode
 *            the new exch code
 */
public void setExchCode(String exchCode) {
    this.exchCode = exchCode;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getComments()
 */
public String getComments() {
    return comments;
}

/**
 * Sets the comments.
 * 
 * @param comments
 *            the new comments
 */
public void setComments(String comments) {
    this.comments = comments;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getPdfConfirmationLink()
 */
public String getConfirmationFileName() {
    return this.confirmationFileName;
}

/**
 * Sets the pdf confirmation link.
 * 
 * @param pdfConfirmationLink
 *            the new pdf confirmation link
 */
public void setConfirmationFileName(String confirmationFileName) {
    this.confirmationFileName = confirmationFileName;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getReferenceNumber()
 */
public String getReferenceNumber() {
    return referenceNumber;
}

/**
 * Sets the reference number.
 * 
 * @param referenceNumber
 *            the new reference number
 */
public void setReferenceNumber(String referenceNumber) {
    this.referenceNumber = referenceNumber;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getStlSts()
 */
public String getStlSts() {
    return stlSts;
}

/**
 * Sets the stl sts.
 * 
 * @param stlSts
 *            the new stl sts
 */
public void setStlSts(String stlSts) {
    this.stlSts = stlSts;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getStlAmtBase()
 */
public double getStlAmtBase() {
    return stlAmtBase;
}

/**
 * Sets the stl amt base.
 * 
 * @param stlAmtBase
 *            the new stl amt base
 */
public void setStlAmtBase(double stlAmtBase) {
    this.stlAmtBase = stlAmtBase;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getSettlDate()
 */
public Date getSettlDate() {
    return settlDate;
}

/**
 * Sets the settl date.
 * 
 * @param settlDate
 *            the new settl date
 */
public void setSettlDate(Date settlDate) {
    this.settlDate = settlDate;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getCcySettl()
 */
public String getCcySettl() {
    return ccySettl;
}

/**
 * Sets the ccy settl.
 * 
 * @param ccySettl
 *            the new ccy settl
 */
public void setCcySettl(String ccySettl) {
    this.ccySettl = ccySettl;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getTradeRef()
 */
public String getUniqueRef() {
    return uniqueRef;
}

/**
 * Sets the trade ref.
 * 
 * @param tradeRef
 *            the new trade ref
 */
public void setUniqueRef(String uniqueRef) {
    this.uniqueRef = uniqueRef;
}

/* (non-Javadoc)
 * @see com.meritservus.domain.TradeTransaction#getB2bRef()
 */
public String getB2bRef() {
    return b2bRef;
}

/**
 * Sets the b2b ref.
 * 
 * @param b2bRef
 *            the new b2b ref
 */
public void setB2bRef(String b2bRef) {
    this.b2bRef = b2bRef;
}

public Date getTimestamp() {
    return timestamp;
}

public void setTimestamp(Date timestamp) {
    this.timestamp = timestamp;
}

public TradeTransactionStatus getTrscnStatus() {
    return trscnStatus;
}

public void setTrscnStatus(TradeTransactionStatus trscnStatus) {
    this.trscnStatus = trscnStatus;
}

public String getCashAccount() {
    return cashAccount;
}

public void setCashAccount(String cashAccount) {
    this.cashAccount = cashAccount;
}

/**
 * @return the counterparty
 */
public String getCounterpartyAccount() {
    return counterpartyAccount;
}

/**
 * @param counterparty the counterparty to set
 */
public void setCounterpartyAccount(String counterpartyAccount) {
    this.counterpartyAccount = counterpartyAccount;
}

/**
 * @return the effectDate
 */
public Date getEffectDate() {
    return effectDate;
}

/**
 * @param effectDate the effectDate to set
 */
public void setEffectDate(Date effectDate) {
    this.effectDate = effectDate;
}

public double getNumberOfSharesNominal() {
    return numberOfSharesNominal;
}

public void setNumberOfSharesNominal(double numberOfSharesNominal) {
    this.numberOfSharesNominal = numberOfSharesNominal;
}

/**
 * @return the tradeType
 */
public TradeTransactionType getTradeType() {
    return tradeType;
}

/**
 * @param tradeType the tradeType to set
 */
public void setTradeType(TradeTransactionType tradeType) {
    this.tradeType = tradeType;
}

/**
 * @return the confTimestamp
 */
public Date getConftimestamp() {
    return conftimestamp;
}

/**
 * @param confTimestamp the confTimestamp to set
 */
public void setConftimestamp(Date confTimestamp) {
    this.conftimestamp = confTimestamp;
}

/**
 * @return the countryOfRisk
 */
public String getCountryOfRisk() {
    return countryOfRisk;
}

/**
 * @param countryOfRisk the countryOfRisk to set
 */
public void setCountryOfRisk(String countryOfRisk) {
    this.countryOfRisk = countryOfRisk;
}

/**
 * @return the reconciled
 */
public boolean isReconciled() {
    return reconciled;
}

/**
 * @param reconciled the reconciled to set
 */
public void setReconciled(boolean reconciled) {
    this.reconciled = reconciled;
}

/**
 * @return the cntrprty
 */
public String getCounterpartyName() {
    return counterpartyName;
}

/**
 * @param cntrprty the cntrprty to set
 */
public void setCounterpartyName(String counterpartyName) {
    this.counterpartyName = counterpartyName;
}

public String getMarketSectorDes() {
    return marketSectorDes;
}

public void setMarketSectorDes(String marketSectorDes) {
    this.marketSectorDes = marketSectorDes;
}

public String getTraderUUID() {
    return traderUUID;
}

public void setTraderUUID(String traderUUID) {
    this.traderUUID = traderUUID;
}


/**
 * @return the accrued
 */
public double getAccrued() {
    return accrued;
}

/**
 * @param accrued the accrued to set
 */
public void setAccrued(double accrued) {
    this.accrued = accrued;
}

 public Object clone() throws CloneNotSupportedException {
        return super.clone();
    }

public double getContractSize() {
    return contractSize;
}

public void setContractSize(double contractSize) {
    this.contractSize = contractSize;
}

public String getGlobalId() {
    return globalId;
}

public void setGlobalId(String globalId) {
    this.globalId = globalId;
}
}

and my Hibernate configuration is next:

properties.put("hibernate.dialect","org.hibernate.dialect.MySQL5Dialect" );
properties.put( "hibernate.autocommit", "false" );         
properties.put( "hibernate.jdbc.batch_size", "1000" );
properties.put( "hibernate.jdbc.fetch_size", "1000" );
properties.put( "hibernate.connection.autocommit", "false" );
properties.put( "hibernate.cache.use_second_level_cache", "false" );
properties.put("hibernate.cache.provider_class","org.hibernate.cache.NoCacheProvider");
properties.put( "hibernate.connection.CharSet", "utf8" );
properties.put( "hibernate.connection.characterEncoding", "utf8" );
properties.put( "hibernate.connection.useUnicode", "true" );
properties.put( "hibernate.enable_lazy_load_no_trans", "true" );

i next hibernate statistic when i do query:

09:45:08.694 [http-nio-8182-exec-4] INFO  o.h.e.i.StatisticalLoggingSessionEventListener - Session Metrics {
47217 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
444974 nanoseconds spent preparing 1 JDBC statements;
8947089681 nanoseconds spent executing 1 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
8725 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)

}

and this query takes me 20SEC! to get 30k rows (list of plain Transaction objects with 20 fields inside) from my DB. I think it is too slow. How can I speed up the query?

Without the entity model it's difficult to say. – Rohit Sep 12 '17 at 15:43 — Rohit, Sep 12 '17 at 15:43

score 1 · Answer 1 · edited Sep 13 '17 at 07:12

At first, I have to make a disclaimer that Hibernate performance depends on many different factors and it is hard to give an exact answer. Especially without an entity model.

But I can give some recommendations and share some experience.

Once I had to optimize a Hibernate query which were fetching 1500 objects and it took 6 seconds to complete.

At first, I checked which queries Hibernate actually run, by enabling SQL logging as described here:

How to print a query string with parameter values when using Hibernate

The log was burdened by queries which were loading all the objects related to the fetching objects. It was because all the fields were serialized, including fields of the related objects (it was a report).

It turned out it was a well-known situation (though not for me that time) called "N+1 problem".

This is the question on this topic:

What is the solution for the N+1 issue in hibernate?

The answers there offer different solutions for this.

I had end up rewriting that query to a native query with joins. New query took only 0.01 sec. Sure, this approach wouldn't automatically work in every case.

So, I recommend to (in order):

Enable SQL query logging
Handle N+1 somehow (if any)
Profile the DB query (i.e. EXPLAIN etc)

Guys, problem was fixed! The problem was not with my hibernate config or java. The problem was with config of Mysql DB! — user1827052, Sep 13 '17 at 08:17

HosseyNJF · Answer 2 · 2017-09-13T07:18:01.613

0

You are querying around 30000 rows... The Hibernate library will Instantiate objects, load data, fill the data from the table and repeat this process 30000 times. If every single object takes 0.0005s seconds to load, this will be a normal time.

I think 0.0005 is pretty good but if you want any more, read this article about optimizing queries.

edited Sep 13 '17 at 07:18

answered Sep 12 '17 at 15:38

HosseyNJF

491
1
6
18

0.5 seconds per object for 30k objects would be 4+ hours, not 20 seconds. – Mark Rotteveel Sep 13 '17 at 07:15
@MarkRotteveel What a miscalculation :| Thank you, I fixed the answer – HosseyNJF Sep 13 '17 at 07:18

20 seconds to load 30k rows from Hibernate, Is it slow?

2 Answers2