queryes orm with tables millions data

Question

I need recommendations so that my query is faster it takes too much and apart the fragment, my table already contains millions of data, which is the best option for this type of case when there are millions of data. I already applied the creation of processes, index but still slow to do the query

pool = mp.Pool(processes=mp.cpu_count())
results = pool.map(filtar_query,json_f) 
def filtar_query(args):

   fecha, argumentos = args[0], args[1]
   query = Modelo.objects.only('fecha','campo2','campo3').filter(fecha__date=fecha).filter(**argumentos).values('fecha','campo2','campo3')
   df= pd.DataFrame(query)
   df = df.groupby(fecha)

in the part where it takes too much time is to make the query that advice would give me

first thing to consider is that you using only() and value() in the same query , they basically behave the same(getting certain fields from model) except only() return more of a models and query return same type as dict() so i would recommend using only values() — Linh Nguyen, Nov 12 '19 at 02:09
and you are using chain filter. Do you want to filter by all the results match everything in both filters ? if you using chain filter then you getting all the results that include `**argumentos` but some won't have `fecha__date` filter as explained in this [post](https://stackoverflow.com/questions/5542874/difference-between-filter-with-multiple-arguments-and-chain-filter-in-django) — Linh Nguyen, Nov 12 '19 at 02:25

score 2 · Answer 1 · answered Nov 12 '19 at 02:27

You'll want to use values instead of only. The only clause in the ORM creates an instance for each record, where values returns a dictionary of all values.

Be sure you have indexes on all columns in your filter clause.

query = Modelo.objects.values(
    'fecha','campo2','campo3'
).filter(
    fecha__date=fecha,
    **argumentos,
)

It will be worth seeing the actual SQL generated, and running an EXPLAIN to see where the query slows down, to ensure you have the proper indexes created to avoid a full table scan.

fecha add db_index= True and campo2 and campo3 its ForeinKey so automaticmanrte adds them as Index — Miranda QY, Nov 12 '19 at 02:57

queryes orm with tables millions data

1 Answers1