In our company there are large set of data and many db tables and we developed the backend by using Django REST and hosted in AWS and S3. Now the problem is , as a large size of data it takes too much time to fetch from the db while using Django ORM. So I need an alternative of this which provides an effective solution by faster access. So what to use incase of this? ORM/cursor/raw_sql/ or something else ?

CodePudding user response：

Cursor might be faster than ORM but have you used database Index for your large query or have you used select_related or prefetch_related for solve N 1 problem? If you are using PostgreSQL, you can increase size of shared_buffer and work_mem in postgresql.conf, they are configs for optimization. If your tables barely changes, you can use Materialized Views in PostgreSQL or you can integrate caching system.

In my opinion, using cursor or raw_sql should be last option because you will lose all benefits from ORM.

CodePudding user response：

The lower you with the DB API, the faster the result, but most of the time, it should be how you handle the ORM. First, when you say it takes too much time to fetch, do you know why ? Is is because of the database layer, orm layer, or you python code ? Is because the database needs indexing, vacuming, or any special operations ? Is that because the orm (or your implementation and use of it) is settings bad queries ? Is it because of multiple queries (lack or bad use of prefetch_related and select_related), are you fetching too much data (big json/binary) and .defer or .values could be used ? Is this because you are retrieving all datas ?

Whatever, I recommend to always do profiling, and then think about what could be optimized. Most of the time, simple optimization would do 80% of the work.

Anyway, you should watch this page : https://docs.djangoproject.com/en/4.0/topics/db/optimization/

and then your database documentation for optimization.