A few half-formed thoughts on SQLObject
I love SQLObject, but this is a rant about the tiny frustrations I face with it.
First, this is a minor point. I don’t really care about database independence that much. Postgres has a lot of wonderful features: I never have to worry about choosing the table engine that will enforce foreign key constraints, I like creating indexes with function inside:
create unique index nodup_parent on category (org_id, parent_cat, lower(name));
and I really like how easy it is to write stored procedures. Anyway, since I know I’m going to use postgresql, I don’t want to be restricted to only the features that exist or can be emulated in every platform. I know all about sqlmeta and createSQL and use it plenty. But I don’t like how when I set a default value, sometimes it is set in the database table, and other times, it isn’t.
Anyway, in practice, the most dangerous part of using SQLObject is that it hypnotizes you into forgetting about the queries behind everything. Imagine you have employees, departments, and a join table between them. You can set this up in SQLObject like this:
class Employee(SQLobject):
name = UnicodeCol(alternateID=True)
departments = RelatedJoin('Department')
class Department(SQLObject):
name = UnicodeCol(alternateID=True)
employees = RelatedJoin('Employee')
You want to draw a grid that indicates whether each user is a member in every group, so you might dash off some code like this:
for emp in Employee.select():
for d in Department.select():
if d in emp.departments:
print "yes!"
else:
print "no!"
In an ideal scenario, you can do this with three simple queries:
- You need a list of employees
- You need a list of departments
- You need the list of employee-department of associations.
People that talk about how you can use outer joins to cram all that into one query will be dropped into a bottomless pit. Besides, I profiled it, and three separate queries is often much cheaper.
Anyway, back to the point. SQLObject will only run a single query to get the employees and a separate single query to get all the departments. So that’s good.
However, the place where all hell breaks loose is that if clause in the middle. If we have three employees and four departments, this statement
if d in emp.departments:
executes a dozen times. That’s unavoidable. The problem is that each time it executes, SQLObject runs a query like:
select department_id from department_employee where employee_id = (whatever);
Every time you say “is this particular department in this employee’s list of departments?” SQLObject grabs the full list of departments for that employee. So, if you ask about 10 different departments, you will run the exact same query ten times. Sure, the database is likely to cache the results of the query for you, but it is still very wasteful.
With just a few employees and a few departments, that’s not so bad. Eventually, though, as the number of employees and departments grow, the cost of that code grows at N2, which is just geek slang for sucky.
So, in conclusion, this may sound like a rant, but it really isnt. SQLObject is great. But it isn’t magic. It’s a great scaffolding system. But now I find that I’m rewriting a fair portion of code in order to reduce the database costs.
Aside: when I started paying attention to the queries generated by SQLObject, I found it really useful to edit postgresql.conf and enable log_min_duration_statement. Then every query and its cost will be logged for you. This is really useful stuff. It’s helped me to relax about doing a lot of things that I used to think were really bad.
