title: Why is loading SQLAlchemy objects via the ORM 5-8x slower than rows via a raw MySQLdb cursor?
body: <p>I noticed that SQLAlchemy was slow fetching (and ORMing) some data, which was rather fast to fetch using bare bone SQL. First off, I created a database with a million records:</p>
  
  <pre><code>mysql&gt; use foo
  mysql&gt; describe Foo;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | id    | int(11) | NO   | PRI | NULL    |       |
  | A     | int(11) | NO   |     | NULL    |       |
  | B     | int(11) | NO   |     | NULL    |       |
  | C     | int(11) | NO   |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  mysql&gt; SELECT COUNT(*) FROM Foo;
  +----------+
  | COUNT(*) |
  +----------+
  |  1000000 |
  +----------+
  mysql&gt; 
  </code></pre>
  
  <p>As a crude test, querying all Foo's takes approximately 2 seconds:</p>
  
  <pre><code>herbert@dev0 ~ $ date; echo 'use foo; select * from Foo;' | mysql -uroot -pxxx &gt; /dev/null; date
  zo apr 20 18:48:49 CEST 2014
  zo apr 20 18:48:51 CEST 2014
  </code></pre>
  
  <p>If I do this in python using MySQLdb this takes a approximately 3 seconds, including the construction of Foo objects:</p>
  
  <pre><code>herbert@dev0 ~ $ python BareORM.py 
  query execution time:  0:00:02.198986
  total time:  0:00:03.403084
  </code></pre>
  
  <p>Which is the output of:</p>
  
  <pre class="lang-py prettyprint-override"><code>#!/usr/bin/python
  # -*- coding: utf-8 -*-
  
  import MySQLdb
  import sys
  import time
  import datetime
  
  class Foo:
      def __init__(self, a, b, c):
          self.a=a; self.b=b; self.c=c;
  
  try:
      start = datetime.datetime.now()
      con = MySQLdb.connect('localhost', 'root', 'xxx', 'foo')
      cur = con.cursor();
  
      cur.execute("""SELECT * FROM Foo LIMIT 1000000""")
      print "query execution time: ", datetime.datetime.now()-start
      foos = [];
      for elem in cur:
          foos.append(Foo(elem[1], elem[2], elem[3]))
      con.commit()
  
  except MySQLdb.Error, e:
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit(1)
  
  finally:
      if con: con.close()
      print "total time: ",  datetime.datetime.now()-start
  </code></pre>
  
  <p>However, using SQLAlchemy to reduce boilerplate code, it needed approximately 25 seconds to do the same job:</p>
  
  <pre><code>herbert@dev0 ~ $ python AlchemyORM.py 
  total time:  0:00:24.649279
  </code></pre>
  
  <p>Using this code:</p>
  
  <pre class="lang-py prettyprint-override"><code>import sqlalchemy
  import datetime
  import MySQLdb
  
  from sqlalchemy import Column, Integer, create_engine
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker, relationship, backref
  
  Base = declarative_base()
  
  class Foo(Base):
      __tablename__ = 'Foo'
      id = Column(Integer, primary_key=True)
      A  = Column(Integer(unsigned=False), nullable=False)
      B  = Column(Integer(unsigned=False), nullable=False)
      C  = Column(Integer(unsigned=False), nullable=False)
  
  engine  = create_engine('mysql+mysqldb://root:xxx@localhost/foo')
  Session = sessionmaker(bind=engine)
  session = Session()
  start = datetime.datetime.now()
  foos  = session.query(Foo).limit(1000000).all()
  print "total time: ", datetime.datetime.now()-start
  </code></pre>
  
  <p><strong>Why does SQLAlchemy operate ~10x slower than the bare SQL solution, assuming that SQLAlchemy should do approximately the same thing? Can I speed things up somehow?</strong></p>
  
  <p>This is a minimal working example of a more complicated query, which joins several tables using eager loading. I was considering just doing simple queries on a single table, and then using dictionaries to create id->object maps and collate one-to-N relations. But before doing so, I want to be sure that SQLAlchemy is unable to perform better, because writing your own ORM is a bad idea from a software design point of view. Imho, a 2x slowdown would be acceptable (maybe).</p>
  
  <p>If you know about other (faster) python-SQL ORM's, or maybe BigTable-alike solutions (that already are the ORM), feel free to mention them as a comment.</p>
  
  <p><strong>EDIT:</strong> Also tried this with Peewee, which resulted in ~15 s.</p>
  
  <pre><code>from peewee import *
  import datetime;
  database = MySQLDatabase("foo", host="localhost", port=3306, user="root", passwd="xxx")
  
  class Foo(Model):
          id = IntegerField()
          A  = IntegerField()
          B  = IntegerField()
          C  = IntegerField()
  
          class Meta:
                  db_table = 'Foo'
                  database = database
  
  start = datetime.datetime.now()
  foos = Foo.select()
  cnt=0;
  for i in foos: cnt=cnt+1
  print "total time: ", datetime.datetime.now() - start
  </code></pre>
  
  <p><strong>EDIT:</strong> As a response to Matthias I tried to do the same thing in Java with Hibernate, the result is approximately 8 to 10 seconds, not exactly fast, but a lot faster than 25 seconds. The code, starting with some classes and ending with some configuration:</p>
  
  <pre class="lang-java prettyprint-override"><code>package herbert.hibernateorm;
  
  import java.util.List;
  
  import org.hibernate.Session; 
  import org.hibernate.Transaction;
  import org.hibernate.SessionFactory;
  import org.hibernate.cfg.Configuration;
  
  public class App {
     public static void main(String[] args) throws Exception {
        SessionFactory factory = new Configuration().configure().buildSessionFactory();
        Session session = factory.openSession();
        Transaction tx = session.beginTransaction();
        long start = System.currentTimeMillis();
        List foos = session.createQuery("FROM Foo").list(); 
        System.out.println(foos.size());
        System.out.printf("total time: %d\n", System.currentTimeMillis() - start);
        session.close();
     }
  }
  </code></pre>
  
  <pre class="lang-java prettyprint-override"><code>package herbert.hibernateorm;
  
  public class Foo {
      private int id, a, b, c;
      public Foo() {}
      public Foo(int A, int B, int C) { this.a=A; this.b=B; this.c=C; }
  
      public int getId() { return id; }
      public void setId(int id) { this.id = id; }
      public int getA() { return a; }
      public void setA(int a) { this.a = a; }
      public int getB() { return b; }
      public void setB(int b) { this.b = b; }
      public int getC() { return c; }
      public void setC(int c) { this.c = c; }
  }
  </code></pre>
  
  <p>The configuration (hibernate.cfg.xml and hibernate.hbm.xml respectively)</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;
  &lt;hibernate-configuration&gt;
    &lt;session-factory&gt;
      &lt;property name="hibernate.dialect"&gt;org.hibernate.dialect.MySQLDialect&lt;/property&gt;
      &lt;property name="hibernate.connection.driver_class"&gt;com.mysql.jdbc.Driver&lt;/property&gt;
      &lt;property name="hibernate.connection.url"&gt;jdbc:mysql://localhost:3306/foo?zeroDateTimeBehavior=convertToNull&lt;/property&gt;
      &lt;property name="hibernate.connection.username"&gt;root&lt;/property&gt;
      &lt;property name="hibernate.connection.password"&gt;xxx&lt;/property&gt;
      &lt;mapping resource="hibernate.hbm.xml"/&gt;
    &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;hibernate-mapping&gt;
      &lt;class name="herbert.hibernateorm.Foo" table="Foo" catalog="foo"&gt;
          &lt;id name="id" type="int"&gt;
              &lt;column name="id" /&gt;
              &lt;generator class="assigned" /&gt;
          &lt;/id&gt;
          &lt;property name="a" type="int"&gt;
              &lt;column name="A" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="b" type="int"&gt;
              &lt;column name="B" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="c" type="int"&gt;
              &lt;column name="C" not-null="true" /&gt;
          &lt;/property&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>And finally the pom file to run it all in maven:</p>
  
  <pre><code>&lt;project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;
      &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
  
      &lt;groupId&gt;herbert&lt;/groupId&gt;
      &lt;artifactId&gt;hibernateORM&lt;/artifactId&gt;
      &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
      &lt;packaging&gt;jar&lt;/packaging&gt;
  
      &lt;name&gt;hibernateORM&lt;/name&gt;
      &lt;url&gt;http://maven.apache.org&lt;/url&gt;
      &lt;repositories&gt;
          &lt;repository&gt;
              &lt;id&gt;unknown-jars-temp-repo&lt;/id&gt;
              &lt;name&gt;A temporary repository created by NetBeans for libraries and jars it could not identify. Please replace the dependencies in this repository with correct ones and delete this repository.&lt;/name&gt;
              &lt;url&gt;file:${project.basedir}/lib&lt;/url&gt;
          &lt;/repository&gt;
      &lt;/repositories&gt;
      &lt;properties&gt;
          &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;
      &lt;/properties&gt;
  
      &lt;dependencies&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;junit&lt;/groupId&gt;
              &lt;artifactId&gt;junit&lt;/artifactId&gt;
              &lt;version&gt;3.8.1&lt;/version&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;version&gt;5.1.21&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.hibernate&lt;/groupId&gt;
              &lt;artifactId&gt;hibernate-core&lt;/artifactId&gt;
              &lt;version&gt;4.0.1.Final&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.hibernate&lt;/groupId&gt;
              &lt;artifactId&gt;hibernate-entitymanager&lt;/artifactId&gt;
              &lt;version&gt;4.0.1.Final&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.hibernate.common&lt;/groupId&gt;
              &lt;artifactId&gt;hibernate-commons-annotations&lt;/artifactId&gt;
              &lt;version&gt;4.0.1.Final&lt;/version&gt;
          &lt;/dependency&gt;   
          &lt;dependency&gt;
              &lt;groupId&gt;nz.ac.waikato.cms.weka&lt;/groupId&gt;
              &lt;artifactId&gt;weka-dev&lt;/artifactId&gt;
              &lt;version&gt;3.7.10&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;commons-configuration&lt;/groupId&gt;
              &lt;artifactId&gt;commons-configuration&lt;/artifactId&gt;
              &lt;version&gt;1.9&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;commons-net&lt;/groupId&gt;
              &lt;artifactId&gt;commons-net&lt;/artifactId&gt;
              &lt;version&gt;3.1&lt;/version&gt;
              &lt;classifier&gt;examples&lt;/classifier&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;com.google.code.gson&lt;/groupId&gt;
              &lt;artifactId&gt;gson&lt;/artifactId&gt;
              &lt;version&gt;2.2.2&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;maven&lt;/groupId&gt;
              &lt;artifactId&gt;maven-jetty-plugin&lt;/artifactId&gt;
              &lt;version&gt;1.1&lt;/version&gt;
              &lt;type&gt;plugin&lt;/type&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;commons-io&lt;/groupId&gt;
              &lt;artifactId&gt;commons-io&lt;/artifactId&gt;
              &lt;version&gt;2.4&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
                  &lt;groupId&gt;com.kenai.nbpwr&lt;/groupId&gt;
                  &lt;artifactId&gt;org-slf4j-jdk14&lt;/artifactId&gt;
                  &lt;version&gt;1.6.1-201106101300&lt;/version&gt;
                  &lt;type&gt;nbm&lt;/type&gt;
          &lt;/dependency&gt;
  
      &lt;/dependencies&gt;
  &lt;/project&gt;
  </code></pre>