Sorting takes a lot of time

[Former user]

I have to sort a table of about 86,000 entries(after filtering). When loaded to client side with JSON(27mb big size hence doing it server side) the sorting and pagination is instantaneous though with server side everytime SQL gets called which is around 84,745,763 entries hence the time delay in response, can a query be stored as JSON on serverside and everytime I want to sort the page I just have to sort the JSON data and not fetch dataset from mysql and then sort.

[Maciej Wisniowski]

I'm not sure if I understand correctly this issue. It doesn't seem to be related to django-datatables-view. I think it might be better to ask about this elsewhere, eg. Stack Overflow.

In general it is possible to store data as JSON and sort it (if its not possible to optimize database queries). Using django_datatables_view you should have a look at get_context_data method and possibly override it to fetch/store data from elsewhere.

[Shashwat Sahay]

Sorry about being vague.

I will try to clarify. So I query the database to populate the datatables via the django webapp for the first time, the module fetches the data from the table, processes it (server-side, pagination among others on the server) and showcases a snippet of data (10 entries) onto the webpage. Now if I want to change page or sort the data or search (within this data set) again it queries the database again, can we somehow store the data returned from the first query in cache and send data from the this cache(which could be json format) each time the user wants to change the page or sort the data again.

Since the data is already loaded onto the memory(server-side) we don't need to query the database again and again(which can be slow with really big database).

Just an Idea don't know if it would be practical though. I will try to implement with get_context_data method.

[Maciej Wisniowski]

It is possible. You need to create proper cache key (possibly one that uses datatables parameters sent in request and user id - for user aware data) and cache the results using it. You should also consider if and how you'll invalidate these caches - this is usually a hard part.