/SQLAlchemy-0.7.8/test/perf/objselectspeed.py

  1import time, resource
  2from sqlalchemy import *
  3from sqlalchemy.orm import *
  4from test.lib.util import gc_collect
  5from test.lib import profiling
  6
  7db = create_engine('sqlite://')
  8metadata = MetaData(db)
  9Person_table = Table('Person', metadata,
 10                     Column('id', Integer, primary_key=True),
 11                     Column('type', String(10)),
 12                     Column('name', String(40)),
 13                     Column('sex', Integer),
 14                     Column('age', Integer))
 15
 16
 17Employee_table = Table('Employee', metadata,
 18                  Column('id', Integer, ForeignKey('Person.id'), primary_key=True),
 19                  Column('foo', String(40)),
 20                  Column('bar', Integer),
 21                  Column('bat', Integer))
 22
 23class RawPerson(object): pass
 24class Person(object): pass
 25mapper(Person, Person_table)
 26
 27class JoinedPerson(object):pass
 28class Employee(JoinedPerson):pass
 29mapper(JoinedPerson, Person_table, \
 30                polymorphic_on=Person_table.c.type, polymorphic_identity='person')
 31mapper(Employee, Employee_table, \
 32                inherits=JoinedPerson, polymorphic_identity='employee')
 33compile_mappers()
 34
 35def setup():
 36    metadata.create_all()
 37    i = Person_table.insert()
 38    data = [{'name':'John Doe','sex':1,'age':35, 'type':'employee'}] * 100
 39    for j in xrange(500):
 40        i.execute(data)
 41
 42    # note we arent fetching from employee_table,
 43    # so we can leave it empty even though its "incorrect"
 44    #i = Employee_table.insert()
 45    #data = [{'foo':'foo', 'bar':'bar':'bat':'bat'}] * 100
 46    #for j in xrange(500):
 47    #    i.execute(data)
 48
 49    print "Inserted 50,000 rows"
 50
 51def sqlite_select(entity_cls):
 52    conn = db.connect().connection
 53    cr = conn.cursor()
 54    cr.execute("SELECT id, name, sex, age FROM Person")
 55    people = []
 56    for row in cr.fetchall():
 57        person = entity_cls()
 58        person.id = row[0]
 59        person.name = row[1]
 60        person.sex = row[2]
 61        person.age = row[3]
 62        people.append(person)
 63    cr.close()
 64    conn.close()
 65
 66def sql_select(entity_cls):
 67    people = []
 68    for row in Person_table.select().execute().fetchall():
 69        person = entity_cls()
 70        person.id = row['id']
 71        person.name = row['name']
 72        person.sex = row['sex']
 73        person.age = row['age']
 74        people.append(person)
 75
 76#@profiling.profiled(report=True, always=True)
 77def orm_select():
 78    session = create_session()
 79    people = session.query(Person).all()
 80
 81#@profiling.profiled(report=True, always=True)
 82def joined_orm_select():
 83    session = create_session()
 84    people = session.query(JoinedPerson).all()
 85
 86def all():
 87    setup()
 88    try:
 89        t, t2 = 0, 0
 90        def usage(label):
 91            now = resource.getrusage(resource.RUSAGE_SELF)
 92            print "%s: %0.3fs real, %0.3fs user, %0.3fs sys" % (
 93                label, t2 - t,
 94                now.ru_utime - usage.last.ru_utime,
 95                now.ru_stime - usage.last.ru_stime)
 96            usage.snap(now)
 97        usage.snap = lambda stats=None: setattr(
 98            usage, 'last', stats or resource.getrusage(resource.RUSAGE_SELF))
 99
100        gc_collect()
101        usage.snap()
102        t = time.clock()
103        sqlite_select(RawPerson)
104        t2 = time.clock()
105        usage('sqlite select/native')
106
107        gc_collect()
108        usage.snap()
109        t = time.clock()
110        sqlite_select(Person)
111        t2 = time.clock()
112        usage('sqlite select/instrumented')
113
114        gc_collect()
115        usage.snap()
116        t = time.clock()
117        sql_select(RawPerson)
118        t2 = time.clock()
119        usage('sqlalchemy.sql select/native')
120
121        gc_collect()
122        usage.snap()
123        t = time.clock()
124        sql_select(Person)
125        t2 = time.clock()
126        usage('sqlalchemy.sql select/instrumented')
127
128        gc_collect()
129        usage.snap()
130        t = time.clock()
131        orm_select()
132        t2 = time.clock()
133        usage('sqlalchemy.orm fetch')
134
135        gc_collect()
136        usage.snap()
137        t = time.clock()
138        joined_orm_select()
139        t2 = time.clock()
140        usage('sqlalchemy.orm "joined" fetch')
141    finally:
142        metadata.drop_all()
143
144
145if __name__ == '__main__':
146    all()