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.
LVL 1
Moti Mashiah.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leakim971PluritechnicianCommented:
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 :
$.ajax("/path/to/endpoint",

handing quantity and page :
now :
$.ajax("/path/to/endpoint?quantiy=10&page=3",

simple UI :
<select name="page">
   <option>1</option>
   <option>2</option>
   <option>3</option>
...
   <option>X</option>
</select>

you can create this with javascript once you get the total number of row : nb_total_row / nb_per_page
0
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.
0
leakim971PluritechnicianCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
0
leakim971PluritechnicianCommented:
the way you did it to only return 500, do the same work to let it return 100
0
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.
0
leakim971PluritechnicianCommented:
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)
0
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?
0
leakim971PluritechnicianCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Moti Mashiah.NET DeveloperAuthor Commented:
k, I got your idea.

Thank you for help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.