Json big data return

Hi Guys,
I am developing asp.net application that returns table of information.
The database the company use is big. When I say big I mean 5000000 million records for a table.

After a research I found some nice js framework to use (https://docs.handsontable.com).

I return JSON data and show on the table, but the issue is when I have big data it goes out of memory.
My question is:
How do I return data efficiently with ajax call?
How do I handle this amount of return?

If you suggest it is paging, then tell me how can I do it through the UI and serverside implementation.

Thank you.
Moti Mashiah.NET DeveloperAsked:
Who is Participating?
I've no idea of you backend logic, I know it must handle that two parameters sent by client to retrieve the exact number of rows requested by the client and from the exact offset from first row number 0. if client send a page instead an offset, just use as offset : page * number_of_results
number_of_results is a static number or should not vary in a user session
If you suggest it is paging, then tell me how can I do it through the UI and serverside implementation.

in your ajax call, you use an endpoint which MUST handle :
- the number of records to send back
- the offset to say from which row you need to send the number of recors requested

so instead having a BIG result from : select * from products;
you have select column1, column2, columnx from products offset 30 rows fetch next 10 rows only
where 10 is the number of result requested
and 30  ask to send the third page (3 x 10)

currently :

handing quantity and page :
now :

simple UI :
<select name="page">

you can create this with javascript once you get the total number of row : nb_total_row / nb_per_page
Moti Mashiah.NET DeveloperAuthor Commented:
Thank you for your answer.

I am doing what you described right now, but the issue is when I have 5000000 rows and I return just the first...let's say 5000 rows and now user go and do sorting, then it will sort just for 5000 rows.

Also, when you do the paging on 5000000 rows it comes very slow even though I request just 5000 at a time.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

you should not request 5000, only what you need to display
create and send 5000 rows of data is too big for your server, bandwidth and client browser and maybe useless until you need to create graphics for a period and not global stats
so request 100,200, just what a basic HTML is able to show in one shot on a 8K (!!!) screen monitor
Moti Mashiah.NET DeveloperAuthor Commented:
k, good point.

Now I return 500 rows per page, so still how am I going to solve the sorting issue.?
Right now I send ajax to the server and get in return 500 rows in the first page,, but the user wants to have all the 5000000 rows as he want to sort etc.
the way you did it to only return 500, do the same work to let it return 100
Moti Mashiah.NET DeveloperAuthor Commented:
I got this point. I just don't get how can I sort between 5000000 rows when I return 500 or 100.
why do you need to "sort" them ?
your SQL query MUST limit this, it's not about client or server code, just update your current SQL query to  handle this (LIMIT and OFFSET)
Moti Mashiah.NET DeveloperAuthor Commented:
So, you say just send a variable back to the server to sort the data and return back to UI?
Moti Mashiah.NET DeveloperAuthor Commented:
k, I got your idea.

Thank you for help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.