0

What I have is a Log table with employee_ids (integers) that references the employee table.

I need to retrieve all logs for a given list of Employee names. The best I can do looks like this:

Log.query.join(Log.employee).filter(Employee.name.in_(['carl', 'joe']))

This works however it's terribly slow, I've learned that IN isn't very good for anything but integers but I'm not sure how to arrange my query to find the ids to use for the IN.

Subbarker
  • 157
  • 2
  • 10
  • Can you provide the SQL that is output from the query, either by setting echo=True in your create_engine call, or doing print Log.query.join(Log.employee).filter(Employee.name.in_(['carl', 'joe'])) Seeing the underlying SELECT will help to determine if there's something that can be done at the sqlalchemy level, or if you might need to add an index to your table schema, or something else – Penguinfrank Feb 24 '15 at 22:30
  • 1
    Sure, this is what I get: 'SELECT "Logs".id AS "Logs_id", "Logs".employee_id AS "Logs_employee_id" FROM "Logs" JOIN "Employees" ON "Employees".id = "Logs".employee_id WHERE "Employees".name IN (:name_1, :name_2)' – Subbarker Feb 24 '15 at 22:50
  • Is it safe to assume the Logs and Employees table are heavily populated? I don't see an issue with unusual joins or anything like that. Also, it's possible to break your query into two steps, with the first getting a list of ids based on the names, then getting the logs based on the ids, but I'd be surprised to find out this wasn't already happening since it's already joining on the id. But it's worth a shot. Another thing to check is to check the execution plan, and update your question with the results. Check https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Penguinfrank Feb 24 '15 at 23:15
  • In this case Employees isn't that large but Logs is massive (millions) and I've done the Execution plan and it's all eaten up by that 'IN', I tried it again using the actual ids directly and the performance jump was exactly what I wanted. Now I need to figure out how to write my query to grab the ids for the IN. – Subbarker Feb 25 '15 at 16:37

0 Answers0