Confirmation of performance loss between Database direct access and standard Django model via DRF Framework #9170
Replies: 5 comments 9 replies
-
|
What is your performance without querying the database? That is your baseline. The database takes 2 seconds roughly, so you cant get any faster than your baseline + time spent in the database. I'm not surprised it is 4.5 times slower when making use of the ORM and serialization tools. In this case you don't need them at all since your datamodel is very straightforward. Find a way to directly output your data to the request like this: https://dev.to/mehdipourfar/faster-csv-export-with-django-postgres-5bi5 |
Beta Was this translation helpful? Give feedback.
-
|
Ok. So I have other table to share via API with more complex relations (Datawarehouse fact and dimensions, bridges and relations many to one and many to many...) |
Beta Was this translation helpful? Give feedback.
-
|
At some point things are slowing down, but what is slow? I mostly don't output 100000 rows through an endpoint so the most of my endpoints aren't taking 10 seconds to respond. But is 10 seconds slow? For a tasks performed once a day I can perfectly wait 10 seconds. If your other queries aren't that heavy (50ms or so) then your endpoint responds in 200ms which is pretty decent. So start benchmarking and keep away of Premature Optimization. Don't benchmark through your browser and use the https://django-debug-toolbar.readthedocs.io/en/latest/ to watch out for n+1 queries. |
Beta Was this translation helpful? Give feedback.
-
|
Yes your right. The default page size is 100 elements per page so quite fast. I took 100000 to show the evidence and page size at 100 multiply the number of request to get all datas so multiply the number of time the http stack is called so I'm using the compromise of 5000 per page on this endpoint |
Beta Was this translation helpful? Give feedback.
-
|
If it historical data just cache it to the disk and update it once a day, or even hourly. Serving static content is way more faster. |
Beta Was this translation helpful? Give feedback.
-
|
I've used the package drf_viewset_profiler that tels me that the main part of delai comes from dispatch function 2023-11-29 11:09:03 Timer unit: 1e-06 s |
Beta Was this translation helpful? Give feedback.
-
|
dispatch() invokes the correct method handler (i.e. get() or update()) so this isn't very helpful. |
Beta Was this translation helpful? Give feedback.
-
Never played with
|
Beta Was this translation helpful? Give feedback.
-
|
In fact serializer takes Total time: 15.6106 s The dispatch function takes Total time: 21.5023 s I'm not used to read this kind of logs and difficult for to unstack function calls. But anyway, on this request, I removed everything that could be customized and so it is the standard/default functions that are used and listed in this dump |
Beta Was this translation helpful? Give feedback.
-
|
Can you share your model, that one with the 4 fields? |
Beta Was this translation helpful? Give feedback.
-
|
Yes sure |
Beta Was this translation helpful? Give feedback.
-
|
@rv2931 you use declared fields or |
Beta Was this translation helpful? Give feedback.
-
|
Ok. It's Index fields, so it is done for improving performance at a certain point so I did it. I've tested with or without indexes directly in my postgres taking the SQL query done by DRF and I must admit that the gain is quite transparent. Guess what big brother put in my mailbox this morning |
Beta Was this translation helpful? Give feedback.
-
|
I expected when the database does the heavy lifting (Elapsed time: 4.52s): from django.db import connection
with connection.cursor() as cursor:
cursor.execute("""select json_agg(api_stgkeyvaluestore)::text from api_stgkeyvaluestore limit 100000""")
row = cursor.fetchone()
with open("sql.json", "w") as f:
f.write(row[0])it outperforms python (Elapsed time: 1.13s): import json
from api.models import StgKeyValueStore
from api.serializers import StgKeyValueStoreSerializer
results = StgKeyValueStore.objects.order_by('id').all()[0:100000]
serializer = StgKeyValueStoreSerializer(results, many=True)
json.dump(serializer.data, open("drf.json", "w"))But is doesn't, interesting. Here is the used serializer & model: from django.db import models
from rest_framework import serializers
class StgKeyValueStore(models.Model):
_id = models.IntegerField(blank=False, null=False, unique=False)
_reference = models.CharField(max_length=64, blank=False, null=False, unique=False)
key = models.CharField(max_length=2048, blank=False, null=False, unique=False)
value = models.TextField()
class Meta:
indexes = [
models.Index(fields=['_id']),
models.Index(fields=['_reference']),
models.Index(fields=['key']),
models.Index(fields=['_reference', 'key']),
]
class StgKeyValueStoreSerializer(serializers.ModelSerializer):
class Meta:
model = StgKeyValueStore
fields = ('_id', '_reference', 'key', 'value') |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Hi
I'm working with Django and DRF Framework to give access to data via api
Now I have a working application but I admint I'm facing to a significant loss of performance
Datas is planned to be access by PowerBi tools because officially giving access directly to database is not a preconisation in our organisation so we prefer to give access via API officially
The situation is I have a 400kw records of as simple PostgreSQL table with 4 columns (key value table)
When I access theses data via a postgresql tool (dbeaver) :
select * from staging.safetycube_occurrence_key_value limit 100000
the fetch of 10000 objects last between 1.9 and 2.5 seconds
When I access to the same datas via Django+drf+LimitOffsetPagination with a limit of 100000 in firefox + debug tools
The waiting phase last between 8.5 and 11s
The for the same request (I think so) loss is about 450% in total time of transaction
So my question is : is it a typical range of performance loss of direct database access to api django+drf access ? or do you think I should have a problem somewhere else ?
Thank you for reading and for your answer
Beta Was this translation helpful? Give feedback.
All reactions