0

I have a web application in which I get data from my database and show in a datatable. I am facing an issue doing this as the data that I am fetching has too many rows(200 000). So when I query something like select * from table_name; my application gets stuck.

Is there a way to handle this problem with JavaScript?

I tried pagination but I cannot figure how would i do that as datatable creates pagination for already rendered data?

Is there a way through which I can run my query through pagination at the backend?

James Z
  • 12,209
  • 10
  • 24
  • 44
Gaurav
  • 69
  • 2
  • 16
  • Your problem is not related to JS, you should do the pagination in server, using SQL, or using your server-side language (e.g C# (LINQ)) – Mehdi Dehghani Mar 05 '19 at 05:31
  • Related question: [What is the best way to paginate results in SQL Server](https://stackoverflow.com/q/109232/3367974) – Mehdi Dehghani Mar 05 '19 at 05:33
  • @MehdiDehghani are you suggesting that i should not fetch all rows at onetime and fetch limited rows using an offset query. – Gaurav Mar 05 '19 at 05:41
  • @MehdiDehghani But still how would i get all the rows and integrate the above method with the datatable (pagination) – Gaurav Mar 05 '19 at 05:42
  • Yes, you should always fetch data that you are going to show to the user, (if you want to show 10 rows, fetch 10 rows, not 11, or more) are you using PHP or C#? or something else (as backend)? – Mehdi Dehghani Mar 05 '19 at 05:51
  • As you say, fetching all data is not feasible, so do pagination at server end. For this you can send few things to server such as pageSize, pageNumber, sortByField etc so that server returns you that page data. – TAB Mar 05 '19 at 05:51
  • Please use universal measurements and not something like "lac" that's just known around India. – James Z Mar 05 '19 at 17:42

3 Answers3

2

I have come across the same problem when working with mongodb and angularjs. I used server side paging. Since you have huge number of records, You can try using the same approach.

Assuming a case that you are displaying 25 records in one page.

Backend:

  1. Get the total count of the records using COUNT query.
  2. select * from table_name LIMIT 25 OFFSET ${req.query.pageNumber*25} to query limited records based on the page number;

Frontend:

  1. Instead of using datatable, display the data in HTML table it self.
  2. Define buttons for next page and previous page.
  3. Define global variable in the controller/js file for pageNumber. Increment pageNumber by 1 when next page button is clicked and decrement that by 1 when prev button is pressed.
  4. use result from COUNT query to put upper limit to pageNumber variable.(if 200 records are there limit will be 200/25=8).

So basically select * from table_name LIMIT 25 OFFSET ${req.query.pageNumber*25} will limit the number of records to 25. when req.query.pageNumber=1, it will offset first 25records and sends next 25 records. similarly if req.query.pageNumber=2, it will offset first 2*25 records and sends 51-75 records.

Surya Tangella
  • 61
  • 2
  • okay i tried this but i tried page numbers rather than next and previous buttons – Gaurav Mar 05 '19 at 06:07
  • i get the number of pages through (total number of rows/number of records to be displayed) and then ran a loop to that number and created that many buttons. – Gaurav Mar 05 '19 at 06:09
  • But the problem i am facing now is that the styling of the buttons is quite clumsy – Gaurav Mar 05 '19 at 06:10
  • @Gaurav you dont need a loop for this. You just need to query what you want to show on the page based on the page number. – Surya Tangella Mar 05 '19 at 06:14
  • Can you share code snippet of what you are trying to do? – Surya Tangella Mar 05 '19 at 06:19
  • var buttons = ''; for(var i=1;i<=numberofpages; i++) { buttons += (''); } – Gaurav Mar 05 '19 at 06:29
  • where gettablepaginated() is the method that do an ajax call to the backend and do an offset query. – Gaurav Mar 05 '19 at 06:30
  • You need not create that many buttons for each page. Create one button. Onclick, it will call a method that will increment page number variable by 1 and make ajax call to the backend. – Surya Tangella Mar 05 '19 at 07:54
  • I declare the page number globally and assigned 1 to it but you see everytime its value becomes 1 – Gaurav Mar 05 '19 at 09:04
  • I used angularjs. $scope.pageNext = function () { $scope.pageNumber = $scope.pageNumber + 1; // ajax calls with updated pageNumber } – Surya Tangella Mar 05 '19 at 09:44
  • Global variable value will be changed everytime you click next page button. How come it will be 1 all the time. – Surya Tangella Mar 05 '19 at 09:52
  • But when we declare it globally we need to first initialise it with some value or not ? – Gaurav Mar 05 '19 at 14:44
  • Yeah initially you declare it as 1. After that whenever you click next button, it will get incremented by 1. Initialisation takes place when page loads for the first time. So after that it's next page button that will modify the value of global variable and makes Ajax calls. – Surya Tangella Mar 05 '19 at 16:22
1

There are two ways to handle.

  1. First way - Handling paging in client side Get all data from database and apply custom paging.
  2. Second way - Handling paging in server side Every time you want to call in database and get records according to pagesize.
Andry
  • 16,172
  • 27
  • 138
  • 246
Hemang A
  • 1,012
  • 1
  • 5
  • 16
  • Can't get all data as it is not feasible and secondly are you suggesting using an offset in that case how would would i handle it on the frontend(datatable) – Gaurav Mar 05 '19 at 05:45
  • First, you will find total records and depending on total record wise display page number with page size (like 10, 5, etc.) in Front ends. After you click on page number then call to the database and according to page wise get all records. and by default get first records according to page size wise. – Hemang A Mar 05 '19 at 05:51
0

You can use LIMIT and OFFSET constraints for pagination in MySQL. I understand that at a time 2 lacs data makes performance slower. But as you mention that you have to use JS for that. So make it clear that if you wants js as frontend then it is not going to help you. But as you mention that you have a web application, If that application is on Node(as server) then I can suggest you the way, which can help you a lot.

use 2 variables, named var_pageNo and var_limit. Now use the row query of mysql as

select * form <tbl_name> LIMIT var_limit OFFSET (var_pageNo * var_limit);

Do code according to this query. Replace the variable with your desire values. This will make your performance faster, and will fetch the data as per your specified limit.

hope this will helpful.

Mayur
  • 4,345
  • 3
  • 26
  • 40