0

I have a few views in Django where I query the database about 50 times. I am wondering if it is faster to query the database or to grab the items once and count myself in python.

For example: On one page, I want to list the following statistics: - users who live in New York - users who are admin users - users who are admin users and have one car ... ... etc.

Is it faster to do many Django database queries such as the following:

User.objects.filter(state='NY').count()
User.objects.filter(type='Admin').count()
User.objects.filter(type='Admin', car=1).count()

Or would it be faster to grab all the users, loop through them in python just one time, and count each of these things as I go?

Does it matter on the database? Does it matter how many of these count() queries I execute (on some views I have upwards of 30 as my application is very data driven and is just spitting out many statistics)?

Thanks!

user2662692
  • 227
  • 5
  • 15
  • We can't answer this, right? How many users are there? Why are there so many statistics on one page? – Simeon Visser Feb 10 '15 at 21:26
  • Depending on how your database is set up there might be a way to do this with a reduced number of queries rather than repeating filter then count. The point is whether the number of round trips to the database causes more performance loss than massaging the data from within Python. – Two-Bit Alchemist Feb 10 '15 at 21:29
  • We have no way of knowing which is faster. If performance is an issue, always start by profiling your application. Optimize the bottlenecks, and profile again. Test with larger datasets as well to see if your solution is scalable. – knbk Feb 10 '15 at 22:06

1 Answers1

1

Use the Database as much as you can.

Python, or Java, or any other language doesn't matter. Always use the SQL (if it is a RDBS ). Every time you execute an SQL multiple things happen:

  • Prepare
  • Execute
  • Fetch

You want every single step to be minimal. If you do all of these on the DB, the Prepare/Execute may take longer (but usually is less than all the queries combined), but Fetch (which sometimes is the biggest) becomes 1 (not n), and usually has also less records that the others combined.

Then, do execution plan to optimize your prepare and execution steps.

Next link is for java developers, but actually it can apply to python world too.

azbarcea
  • 3,323
  • 1
  • 20
  • 25