Announcement Announcement Module
Collapse
No announcement yet.
Pagination performace problem in Spring Data/Jpa. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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:

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

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

    Code:
    @Controller
    @RequestMapping("/accounts")
    public class AccountController {
    	@Autowired
    	private AccountRepository accountRepository;
    
    	@Autowired
    	private CustomerRepository customerRepository;
    
    	private final int pageSize = 20;
    
    	private static final Logger logger = LoggerFactory
    	    .getLogger(AccountController.class);
    
    
    	@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;
    		
    		stopWatch.start();
    		
    		page = new PageRequest(pageNumber, pageSize, Direction.ASC,
    		    "customer.lastname", "customer.firstname");
    
    		accounts = accountRepository.findAll(page);
    
    		model.addAttribute("accounts", accounts);
    		stopWatch.stop();
    		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";
    	}
    }
    View

    Code:
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <%@ 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}" />
    
    <html>
    <head>
    <title>Page</title>
    
    <style type="text/css">
    .navlist li {
    	display: inline;
    	list-style-type: none;
    	padding-right: 20px;
    }
    </style>
    </head>
    <body>
    	<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>
    
    	<div>
    		<table width="100%" border="0">
    			<thead>
    				<tr>
    					<td>Counter: ${accounts.getTotalElements()}</td>
    					<td>Id</td>
    					<td>Expiration Date</td>
    					<td>Firstname</td>
    					<td>Lastname</td>
    				</TR>
    			</thead>
    			<tbody>
    				<c:forEach items="${accounts.getContent()}" var="item"
    					varStatus="status">
    					<tr class="${status.count % 2 == 0 ? 'even' : 'odd'}">
    						<td>${status.count})</td>
    						<td>${item.id}</td>
    						<td>${item.expiryDate}</td>
    						<td>${item.customer.firstname}</td>
    						<td>${item.customer.lastname}</td>
    						<td></td>
    					</tr>
    				</c:forEach>
    			</tbody>
    		</table>
    	</div>
    
    	<div>
    		<ul class="navlist">
    			<c:choose>
    				<c:when test="${currentIndex == 1}">
    					<li class="disabled"><a href="#">&lt;&lt;</a></li>
    					<li class="disabled"><a href="#">&lt;</a></li>
    				</c:when>
    				<c:otherwise>
    					<li><a href="${firstUrl}">&lt;&lt;</a></li>
    					<li><a href="${prevUrl}">&lt;</a></li>
    				</c:otherwise>
    			</c:choose>
    			<c:forEach var="i" begin="${beginIndex}" end="${endIndex}">
    				<c:url var="pageUrl" value="/accounts/list/${i}" />
    				<c:choose>
    					<c:when test="${i == currentIndex}">
    						<li class="active"><a href="${pageUrl}"><c:out
    									value="${i}" /></a></li>
    					</c:when>
    					<c:otherwise>
    						<li><a href="${pageUrl}"><c:out value="${i}" /></a></li>
    					</c:otherwise>
    				</c:choose>
    			</c:forEach>
    			<c:choose>
    				<c:when test="${currentIndex == accounts.totalPages}">
    					<li class="disabled"><a href="#">&gt;</a></li>
    					<li class="disabled"><a href="#">&gt;&gt;</a></li>
    				</c:when>
    				<c:otherwise>
    					<li><a href="${nextUrl}">&gt;</a></li>
    					<li><a href="${lastUrl}">&gt;&gt;</a></li>
    				</c:otherwise>
    			</c:choose>
    		</ul>
    	</div>
    
    	${timingLog}
    
    </body>
    </html>

    Thank you in advance.

    Andrea

  • #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?

    Comment

    Working...
    X