Announcement Announcement Module
No announcement yet.
Pagination performace problem in Spring Data/Jpa. Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pagination performace problem in Spring Data/Jpa.

    Hi all,
    I'm using Spring Data Jpa/Hibernate to load a paged list of 20 items from a MySql database with 200.000 records.
    I'm experiencing very poor performace, to load a page of 20 record it takes about 4/5 seconds per request.
    I've tried to do the same task using .NET/Entity framework against the same database, in this case it takes about 60/70 milliseconds.
    There is no comparison between the two solutions.
    Am I doing something wrong or is there something wrong in the default implementation of Pagination in Spring Data?

    Here my code:

    @Table(name = "Account")
    public class Account {
    	private Long id;
    	@OneToOne(mappedBy = "account", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    	private Customer customer;
    	private Date expiryDate;
    	public Long getId() {
    		return id;
    	public Customer getCustomer() {
    		return customer;
    	public Date getExpiryDate() {
    		return expiryDate;
    	public void setId(Long id) { = id;
    	public void setCustomer(Customer customer) {
    		this.customer = customer;
    	public void setExpiryDate(Date expiryDate) {
    		this.expiryDate = expiryDate;
    @Table(name = "Customer")
    public class Customer {
    	private Long id;
    	private String firstname;
    	private String lastname;
    	Account account;
    	public Long getId() {
    		return id;
    	public String getFirstname() {
    		return firstname;
    	public String getLastname() {
    		return lastname;
    	public void setId(Long id) { = id;
    	public void setFirstname(String firstname) {
    		this.firstname = firstname;
    	public void setLastname(String lastname) {
    		this.lastname = lastname;

    public interface AccountRepository extends JpaRepository<Account, Long> {
    MVC Controller

    public class AccountController {
    	private AccountRepository accountRepository;
    	private CustomerRepository customerRepository;
    	private final int pageSize = 20;
    	private static final Logger logger = LoggerFactory
    	@RequestMapping(value = "/list", method = RequestMethod.GET)
    	public String list(Model model) {
    		return this.list(0, model);
    	@RequestMapping(value = "/list/{pageNumber}", method = RequestMethod.GET)
    	public String list(@PathVariable Integer pageNumber, Model model) {
    		String timingLog = "";
    		StopWatch stopWatch = new StopWatch();
    		PageRequest page;
    		Page<Account> accounts;
    		page = new PageRequest(pageNumber, pageSize, Direction.ASC,
    		    "customer.lastname", "customer.firstname");
    		accounts = accountRepository.findAll(page);
    		model.addAttribute("accounts", accounts);
    		timingLog += "Load Accounts: " + stopWatch.getTotalTimeMillis() + "; ";
    		int current = accounts.getNumber() + 1;
    		int begin = Math.max(1, current - 5);
    		int end = Math.min(begin + pageSize, accounts.getTotalPages());
    		model.addAttribute("beginIndex", begin);
    		model.addAttribute("endIndex", end);
    		model.addAttribute("currentIndex", current);
    		model.addAttribute("timingLog", timingLog);
    		return "accounts.list";

    <%@ taglib prefix="c" uri=""%>
    <%@ page session="false"%>
    <c:url var="firstUrl" value="/pages/1" />
    <c:url var="lastUrl" value="/pages/${accounts.totalPages}" />
    <c:url var="prevUrl" value="/pages/${currentIndex - 1}" />
    <c:url var="nextUrl" value="/pages/${currentIndex + 1}" />
    <style type="text/css">
    .navlist li {
    	display: inline;
    	list-style-type: none;
    	padding-right: 20px;
    	<h1>List Accounts Using Jpa Repository</h1>
    	<a href='<c:url value="/accounts"/>'>Accounts Home</a>
    	<a href='<c:url value="/accounts/add.form"/>'>Add Accounts</a>
    		<table width="100%" border="0">
    					<td>Counter: ${accounts.getTotalElements()}</td>
    					<td>Expiration Date</td>
    				<c:forEach items="${accounts.getContent()}" var="item"
    					<tr class="${status.count % 2 == 0 ? 'even' : 'odd'}">
    		<ul class="navlist">
    				<c:when test="${currentIndex == 1}">
    					<li class="disabled"><a href="#">&lt;&lt;</a></li>
    					<li class="disabled"><a href="#">&lt;</a></li>
    					<li><a href="${firstUrl}">&lt;&lt;</a></li>
    					<li><a href="${prevUrl}">&lt;</a></li>
    			<c:forEach var="i" begin="${beginIndex}" end="${endIndex}">
    				<c:url var="pageUrl" value="/accounts/list/${i}" />
    					<c:when test="${i == currentIndex}">
    						<li class="active"><a href="${pageUrl}"><c:out
    									value="${i}" /></a></li>
    						<li><a href="${pageUrl}"><c:out value="${i}" /></a></li>
    				<c:when test="${currentIndex == accounts.totalPages}">
    					<li class="disabled"><a href="#">&gt;</a></li>
    					<li class="disabled"><a href="#">&gt;&gt;</a></li>
    					<li><a href="${nextUrl}">&gt;</a></li>
    					<li><a href="${lastUrl}">&gt;&gt;</a></li>

    Thank you in advance.


  • #2
    Can you elaborate on what it means to "do the same thing"? We trigger a count query to be able to calculate the total number of pages available, which you might have forgotten in the .NET case. Do you have the appropriate indexes available on the database?