django-rest-framework-datatables slow TTFB (serverside, 3.5 million rows, 9 columns)

chainwang31

Hi, I'm new to datatables (Thanks for the product!:)) and django, trying to build a website hosting 3.5 m rows with 9 columns. I am currently using django-rest-framework-datatables (by izimobil, David Louis). The table display/functions are okay, but is slow on page load and search:

Page load: Around 8-9 sec of "time to first byte", which takes >99% of the loading time, others are in milli-sec;
Search: takes 10-40 sec of "time to first byte" on search (1 draw only with search delay) - I use column individual header search only

Any idea what I can look to improve this (queryset? database/hardware performance)? (I use datatables 1.10.19 django 2.2 python 3.7, sqlite hosting all the data, 50 rows per page)

js:
serverSide: true,
ajax: {
url: "/api/mydata/?format=datatables",
}

views.py
class mydataViewSet(viewsets.ModelViewSet):
queryset = mydata.objects.all()
serializer_class = mydataSerializer

kthorngren

Page load: Around 8-9 sec of "time to first byte", which takes >99% of the loading time, others are in milli-sec;

Are you saying that 99% of the time the client is waiting for the data and once received it takes milliseconds to render the page?

If so then the delay is in the server side and not Datatables in the client. You would need to determine if the problem is with the sqlite speed, sqlite queries or if its something in the django-rest-framework-datatables library. These questions would be better answered in their respective support forums.

Kevin

chainwang31

Hi Kevin,
Thank you for your response. Please see the timing analysis below (initial page load). I am not sure if this means delay in sqlite.

kthorngren

Here is an article on TTFB that may help. Essentially it says that number is the time the browser waits until it receives the response from the server. This is the time that Datatables needs to wait before it receives data to render the page.

I'm not terribly familiar with sqllite and its capabilities/performance for efficiently handling a large DB. I would start by determining the sql queries used by django-rest-framework-datatables and try them manually. How long do they take?

Depending on that answer would determine my next step. Maybe the queries used by django-rest-framework-datatables need to be changed to be better optimized for sqlite. Likely they use LIMIT and OFFSET to support server side processing. Do these work well with a large sqlite DB? Maybe sqlite is not the best option for 3.5 m rows (I'm not sure).

The Datatables developers may chime in with comments about sqlite but I don't think they do much with Python testing as it is independent of Datatables.

Kevin

colin

This section of the FAQ may help, it discusses various techniques to improve performance,

Cheers,

Colin