Talk Notes: Wesley George The Limits of the ORM

Wesley George is a Technical Lead for a Startup in Canada called Clearbanc. The impetus from this talk came from his trying to use the ORM for a complex query and marveling at how slow it was. Thus, he put together this talk based on his adventures in deeply diving into SQL.

An ORM is Cool, but…

ORMs shine when they remove boilerplate code. They provide decent aggregation, but generally only for single table solutions. However, for more complex tasks, using complex SQL queries in a relational database allows you to create extremely powerful and performant aggregations.

Example 1: Simple Signups by Month

You might start with some code that looks like this:

This would run extremely slow against a sufficiently large or complex dataset, so it behooves us to dive into the SQL to run the computation. However, writing complex SQL allows us to do this in a much more performant way.

The WITH keyword allows us to essentially create ephemeral tables for use in a query. They are similar to subqueries, except they result in a much simpler final SELECT statement at the end.

Example 2: User Engagement Change Segmented by Campaign

For this example, we won’t even attempt to use the ORM.

Annotators note: Sorry, I’m not gonna type out the SQL for all this…

Concerning Efficiency and Managing Complexity

The technology inside of a relational dataase represents decades of computer science research. However, as your data grows into the terabyte and petabyte range, you will need to manage your complexity. Wesley describes a method and a mentions a tool for this.

  • Data Warehousing is the process of storing intermediate aggregations or representations of the data on a periodic basis (hourly, daily, weekly, etc).
  • SQLAlchemy is a lower-level SQL python package, allowing a comfortable code-based medium between an ORM and raw queries.
SQL Alchemy provides an alternative to an ORM, allowing you to represent SQL-like queries in your code.
SQL Alchemy provides an alternative to an ORM, allowing you to represent SQL-like queries in your code.

Leave a Reply

Your email address will not be published. Required fields are marked *