Dandelion Datatables very slow with server side processing and pagination enabled

Question

UPDATE

If the number of rows decreases the waiting time decreases as well. If the server side processing is enabled why is this happening? I have 90000 rows but I expect to insert over 100 million. How will this change the software performance? Is dandelion datatable able to cope with that?

QUESTION

I am running dandelion datatables version 1.1.0 on my spring mvc application. The web container is tomcat 8. I am using java 8.

I have two main problems

The datatable is very slow. I am displaying the whole table which has got 90000 rows from the database and when I press the next page button it takes 1.5 seconds to refresh the data. Server side processing and pagination are enabled as well.

The chrome console shows the following:

According to google TTFB is:

Time spent waiting for the initial response, also known as the Time To First Byte. This time captures the latency of a round trip to the server in addition to the time spent waiting for the server to deliver the response.

A ping to the server, where mysql database is, shows this:

icmp_seq=1 ttl=64 time=0.206 ms
icmp_seq=2 ttl=64 time=0.232 ms
icmp_seq=3 ttl=64 time=0.188 ms
icmp_seq=4 ttl=64 time=0.192 ms

For me this is not bad at all

When I open the page where the table is, the page shows only the table without theme and without data. I have to press the refresh page button to see the data and the bootstrap theme.

Please see some code below for your information

Controller

@RequestMapping(value = "/results")
    public @ResponseBody DatatablesResponse<Results> findAllForDataTables(HttpServletRequest request) {
      DatatablesCriterias criterias = DatatablesCriterias.getFromRequest(request);
      DataSet<Results> results = this.resultsServiceimpl.findResultsWithDatatablesCriterias(criterias);

      return DatatablesResponse.build(results, criterias);
    }

Page.jsp

<datatables:table id="myTableId" url="/results" serverSide="true" pageable="true" theme="bootstrap2" cssClass="table table-striped" >
    <datatables:column title="ID" property="id" />
    <datatables:column title="Start Date" />
    <datatables:column title="Stop Date"/>
</datatables:table>

IMPORTANT: The database table has got 31 columns and 90000 rows most of them empty. The datatable however is showing only the first three columns. Could this be the problem?

Hibernate query when the "Next button" is pressed (3 queries)

Hibernate: select result0_.id as id1_0_, result0_.start_date as start_date2_0_, result0_.stop_date as as stop_date3_0_,, ....other fields.... from results result0_ order by result0_.id ASC limit ?, ?

Hibernate: select count(result0_.id) as col_0_0_ from results result0_

Hibernate: select result0_.id as id1_0_, result0_.start_date as start_date2_0_, result0_.stop_date as as stop_date3_0_,, ....other fields.... from results result0_

Can you see anything wrong here?

Thank you for your help

Dandelion Datatables very slow with server side processing and pagination enabled

0 Answers0