Clever Geek Handbook

Django objects.filter how is it "expensive"?

I am trying to do a search in Django. It's a search form with a freetext input + some options to choose from so you can filter by year and so on. This is the piece of code I have in view so far, the piece that does the filtering. And I would like to get information on how expensive it will be on the database server.

soknad_list = Soknad.objects.all() 

if var1:
    soknad_list = soknad_list.filter(pub_date__year=var1)

if var2:
    soknad_list = soknad_list.filter(muncipality__name__exact=var2)

if var3:
    soknad_list = soknad_list.filter(genre__name__exact=var3)

# TEXT SEARCH
stop_word_list = re.compile(STOP_WORDS, re.IGNORECASE)
search_term = '%s' % request.GET['q']
cleaned_search_term = stop_word_list.sub('', search_term)
cleaned_search_term = cleaned_search_term.strip()
if len(cleaned_search_term) != 0:
    soknad_list = soknad_list.filter(Q(dream__icontains=cleaned_search_term) | Q(tags__icontains=cleaned_search_term) | Q(name__icontains=cleaned_search_term) | Q(school__name__icontains=cleaned_search_term))

code>

So what I do is first create a list of all objects, then I check which variables exist (I take them from GET to an earlier point) and then filter the results if they exist. But that doesn't seem too graceful, it probably does a lot of queries to achieve the result, so is there a better way to do this?

It does exactly what I want, but I think there is a better / smarter way to do it. Any ideas?

+1


source to share


3 answers


the filter itself does not execute the request, the request is not executed until you explicitly retrieve the items from the request (like get) and also execute it (the request).



+4


source


You can see the request that will be generated with:

soknad_list.query.as_sql()[0]


You can then put that in your database wrapper to see how long the query will take, or use EXPLAIN (if your database backend supports it) to see how expensive it is.

+2


source


As Aaron mentioned, you should get the query text to run against the database and use EXPLAIN (or some other method) to view the query execution plan. After you run the execution plan for the query, you will see what is happening in the database itself. There are many operations that look very costly to execute procedural code that are very trivial for any database to run, especially if you provide indexes that the database can use to speed up your query.

If I read your question correctly, you will get a result set of all the rows in the Soknad table. Once you get back to those results, you use the filter () method to trim the results that match your criteria. From looking at the Django documentation, it looks like this will make the filter in memory rather than re-querying the database (of course, it really depends on which data access layer you are using, not Django itself).

The most optimal solution would be to use a full text search engine (Lucene, ferret, etc.) to handle this for you. If this is not available or practical, your best bet is to build a query predicate (WHERE clause) before submitting your query to the database and letting the database filter.

However , as with anything database-related, the real answer is "it depends". The best suggestion is to try out several different approaches using data close to production and compare them at least 3 iterations before proceeding with the final solution to the problem. It may be just as fast or even faster to filter in memory rather than filter in the database.

-1


source






More articles:


All Articles