Server side pagination without database call.

nshah

Our application is very much database driven. The implementation of datatable we are thinking is as follows:
If result is < 500 use client side pagination, search, sorting, etc.
Otherwise use Server side call for pagination, search, sorting, etc.
Fetch data first time from database and use server side memory to perform tasks and return results as needed by client.

The datatable could have 10 rows to 25000 rows based on the user input on screen.
We have about 15 different pages each one is a different query for this type of datatable.
We do not want database to be over loaded by these query's, as the database is also used for lot of other purposes. The server side is clustered and have more than enough memory to handle any memory requirement.
These queries are just a 2% to 5% of the application which uses the database.

Question:
How to do following in JAVA servlet?
Fetch data first time from database and use server side memory to perform tasks and return results as needed by client.

allan

I don't really understand your question I'm afraid. If you want some tables to be server-side processing, you would just enable the serverSide option for those tables. Of course you need to know at initialisation time if you want to use server-side processing or not.

Allan

nshah

Thanks Allan for your quick reply.
Yes, I understand the process for serverside option.
My concern is database access performed by datatable every time next page asked or used global search or sorted the table columns.
We want to prevent these database access. As the database is also serving other much more complicated and more important tasks. These frequent queries will slow down the database / trigger performance issues.

Is there any way we can use hash table or similar in java to store the result of the initial query and datatable can use this hash object to perform it's task server side?

allan

I'm afraid I still don't understand. You want to pull all of the information out of the database initially, but you don't want to send it all to DataTables? You want to store the information in memory, on the server, and perform the sorting, filtering, etc as requests come in from that data?

You can do that, but you would need to write all of the sorting and filtering logic, whereas the database would do that for you and be far more optimised.

Allan

nshah

Thanks Allan again for your quick reply.
You understood the requirement correctly.
Does datatable have server side code similar to java script code it has for client side, which I can utilize?

allan

No - I'm afraid such code is not part of the DataTables project. You would need to create that yourself if you needed it.

I'm really surprised that you can't just use the SQL engine to do the paging and sorting. It will take some serious coding to be able to out perform an SQL engine for those operations - creating a new SQL engine for example :-).

Allan

nshah

Allan, Thanks again for your quick response.

We are not worried about the performance of these pages, as they are just search result pages. But we do not want to overload database for this unnecessary repeated searches. The database engine is already overloaded with other more critical real time jobs. These jobs cannot be affected.

We will rather use client side pagination with more filtered search criteria and degraded performance for big searches.