How Can I Pull 900K Records From My Database & Not Have The Page Slow Down When Rendering The Record
I have a project that is pulling 900K records from a database & displaying them to a view. Whenever I pull these records, I get a connection timeout error. I fixed that by pulling the top 50000 records, it works but then I have a problem that it takes time to fully display the page. I also have a problem that I'm displaying these records using DataTables & takes time to load the JavaScript. How can I fix this?
39 Replies
Whyever would you be pulling this many records all at once...?
Use pagination
rendering 900K records in a datatable will never ever be fast lol
and also entirely useless if you ask me
its just too much data to meaningfully work with in a browser/manually
you could offer an export of data instead
It's required for the user to see all these records & choose which ever record they want to use
Hw would they be even able to pick one record out of 900k lmao
You can't even read that many and parse them
Can you redirect me to where I can learn about this
lmao! i figured it out the hard way
Are you using EF or raw SQL?
mhhh! maybe export the data to an excel file or so?
they will search using an ID & the datatable will filter the records because it has this feature
Filter when fetching the data
Search when fetching the data
yeah, CSV would be a solid choice and works with excel
one solution is pagination, how does this work?
and yeah, if the user alreayd knows the ID, just have them enter it before you fetch the data
You get the first 50 records
Then the second batch of 50
pagination just "chunks" the results into managable bits - it will not help in this case
Third batch of 50
and so on
Requested by the user
Raw SQL, I used EF but then i needed to pull from multiple databases so i turned to raw SQL
pagination is for browsing data, not searching for it
Well, yeah
The query will search for the data and filter/sort it
no! after the data is fully loaded
But pagination will help display it, should the filtered search results be a lot
Why?
i did work on this feature, i will try to implement it again on this project
ohhh! search before loading the data
is there a sql script for using pagination?
LIMIT and OFFSETno, i think i will revert back to just searching for the specific data that a user wants & display that record rather than requesting all 900k records
Or, in some databases, just
LIMIT with two parametersohhhh! i will try to use it. so, i assume when you press the next button in the view it will go back to the sql script and laod the next batch?
Yes
well, thank you for explaining this scenario. it absolutely helped me
if you want to simulate that you actually pulled off 900k records you can do a count and show the amount of records like:
Viewing 1-50 of 999.999 records.
You can also simulate paging if u want, like show 1,2,3.......890 with arrows
the datatable plugin has the pagination feature, if you are pulling lets say 10k records it will automatically do it for you but my issue is behind the scenes which is my SQL code. I am using which pulls all the columns and the records from this table and I get the connection timeout from MSSQL Server because pulling a lot of data at once & trying to display it
I mean, we discussed this when you first asked - pagination is for browsing data.
You cant meaningfully browse 900K records
do the search before load thing we discussed.
yes, i was just telling him that the datatables plugin does pagination for me but my problem is just the connection timeout!
command timeout you mean?
i did implement the search feature but on another page it's just not suitable. i need to pull all those records whether i like it or not and the user just can't search because he doesn't know the information / doesn't have the info because this table is just filled with properties that are about 900K
connection timeout is different 🙂
i am lost with this one, what does do?
connection timeout is how long the client waits to establish the connection to the server
command timeout is how long it waits for a command to run
okay, let me run & see what the error name is again so i can confirm
You can still do pagination, no?
yes, i can do but with a limited number of records, 20K records max
Do it with even fewer items per page
50-100 is usually the maximum