CODEWITHSUNDEEP
sqldjangoperformancedatabase-design
shevski
shevski

Reputation: 1022

filter of table of two foreign keys works slow

*corrently I can't add code because I'm not near that computer

Simple Model consists: 

class Test
    static data about test
class Site
    static data about site
class Result
    foreign key Site
    foreign key Test
    startTime
    result

Arguments:

   listTest,
   listSite,
   listStatus

I've made a query that returns with above list returns Test querySet that represents an intersection of the requirements.

I want now to make a query
that returns with the same given lists
only Tests that the last result is in listStatus

I've made a for loop and if I use it on more then 5K test results it takes above a minute.

Upvotes: 0

Views: 196

Answers (1)

Daniel Lyons
Daniel Lyons

Reputation: 22803

It's difficult from your question to know what you're dealing with, both in terms of the code, the database itself, and the query, so this is just my best guess.

If you're causing a query to be executed within a loop, you have the N+1 query problem. It looks from this data like you can do a join instead. However, I'm not quite sure what your database structure looks like or what exactly you want your query to return, so I can't give you an SQL solution. I will say this: many databases do not add indexes to foreign key columns automatically. Therefore I would recommend the following steps:

  1. Make sure both foreign key columns on the result table are indexed separately. Without this, join performance will suffer.
  2. Rewrite your loop to instead perform one query against the database.

Databases are surprisingly efficient at processing complex queries and returning large amounts of data. If you're querying in a loop, you're probably spending most of your time generating and parsing SQL, and marshalling data to and from the database. Using large queries that return large results is usually a lot more efficient than repeatedly using small queries that return small results.

Upvotes: 1

Related Questions