Datatable, Edge and 50k+ records

Hi Guys,

Is there any way I can ensure my datatable doesn’t take forever to load all the records which my controller has passed to my Edge template. Data currently takes a long time to load.

Thanks in advance

Maybe you need to apply eager loading.

There are a few questions that we probably need answers to before being able to answer your question.

Was your database created using migrations or is it an existing database?
What type of database are you using (MySQL, SQLite etc)?

When you get above a certain number of records and you are joining other tables and filtering, indexing of your data becomes more important. Have you checked that the fields you are joining on and which you are applying filters or sorting to are indexed?

2 Likes

Database was created using migrations
Type of DB is MySQL

Not all fields are indexed

You should definitely try indexing the fields that you are using as foreign keys and are filtering on or sorting on. You don’t have to index every field. I have seen a single index reduce a query time from over a minute to 200 milliseconds.

I am already using eager loading

const applicantQuestionnaire = await Questionnaire.query()
        .where('is_deleted', false)
        .with('applicant.level_of_education')
        .with('applicant.source_of_discovery')
        .with('applicant.applicant_status')
        .fetch()

@simonjcarr I don’t consider the query time the problem even though I will implement your suggestion. The problem is more in the context of Datatables loading all the fetched records at once so it takes a while for the table to load and then you can perform filters and all that.

There isn’t any clear documentation on how to pull off Server Side Processing using AdonisJS and Datatables which is what I need assistance for here.

Sorry if my initial request wasn’t very clear.

Looking forward to any help with this, thanks. @virk

Sorry for the delay in getting back you. I’m sure you already know that the with statements in your code are what cause all the records to be fetched upfront. Why don’t you break your query down into multiple separate queries, only pull the data you need and maybe even add paging?