Why is Django ORM so slow?

Question

1. This code - raw SQL - takes 2.6 sec:

  *
    all_feeds = Feed.objects.all()

    for feed in all_feeds:
        q_sku = MainData.objects.raw(f'SELECT id as id, COUNT(DISTINCT sku) as "count" FROM imports_maindata WHERE feed_id={feed.id}')
        q_loc = MainData.objects.raw(
        f'SELECT id as id, COUNT(DISTINCT locale) AS "count" FROM imports_maindata WHERE feed_id={feed.id}')
        q_spec = MapSpecs.objects.raw(
        f'SELECT id as id, COUNT(DISTINCT f_feat_id) AS "count" FROM imports_mapspecs WHERE feed_id={feed.id}')
        q_mapped = MapSpecs.objects.raw(
        f'SELECT id as id, COUNT(DISTINCT ic_feat_id) AS "count" FROM imports_mapspecs WHERE feed_id={feed.id} AND ic_feat_id IS NOT NULL')
    
        q_date = MainData.objects.raw(
        f'SELECT id as id, MAX(last_update) as "last_date" FROM imports_maindata WHERE feed_id={feed.id}')
        print(q_sku[0].count, q_loc[0].count, q_spec[0].count, q_mapped[0].count, q_date[0].last_date)*

3. While this one - ORM only - takes 3.1 sec:

    *f = Feed.objects.all()
    for feed in f:
        prods_count = f.filter(maindata__feed_id=feed.id).values('maindata__sku').distinct().count()
        
        locales_count = f.filter(maindata__feed_id=feed.id).values_list('maindata__locale', flat=True).distinct()
    
        total_specs = f.filter(mapspecs__feed_id=feed.id).count()
        mapped_specs = f.filter(mapspecs__feed_id=feed.id, mapspecs__ic_feat_id__isnull=False).all().count()
    
        try:
            last_update = f.filter(maindata__feed_id=feed.id).values('maindata__last_update').distinct().order_by('-maindata__last_update').first()['maindata__last_update']
        except TypeError:
            pass*

3. And this one, using ORM but different approach, is returned in 3.1-3.2 sec:

    *

    f = Feed.objects.all()
    prods = f.annotate(num_prods=Count('maindata__sku', distinct=True))
    locs = f.annotate(num_locs=Count('maindata__locale', distinct=True))
    total_sp_count = f.annotate(num_books=Count('mapspecs__f_feat_id', distinct=True))
    total_sp_mapped = f.filter(mapspecs__ic_feat_id__isnull=False).annotate(
            num_books=Count('mapspecs__ic_feat_id', distinct=True))
    dates = f.annotate(num_books=Max('maindata__last_update'))

*

So how come that Django ORM is so inefficient and slow? The timings are for a low number of rows in DB (below 50K)... So it's not only slower than raw SQL but has a more confusing (and sometimes too vague) syntax. Guess some other Python frameworks should be considered...

Why is Django ORM so slow?

0 Answers