Source code for examples.performance.large_resultsets
"""In this series of tests, we are looking at time to load a large numberof very small and simple rows.A special test here illustrates the difference between fetching therows from the raw DBAPI and throwing them away, vs. assembling eachrow into a completely basic Python object and appending to a list. Thetime spent typically more than doubles. The point is that whileDBAPIs will give you raw rows very fast if they are written in C, themoment you do anything with those rows, even something trivial,overhead grows extremely fast in cPython. SQLAlchemy's Core andlighter-weight ORM options add absolutely minimal overhead, and thefull blown ORM doesn't do terribly either even though mapped objectsprovide a huge amount of functionality."""fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportIdentityfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportBundlefromsqlalchemy.ormimportSessionfrom.importProfilerBase=declarative_base()engine=NoneclassCustomer(Base):__tablename__="customer"id=Column(Integer,Identity(),primary_key=True)name=Column(String(255))description=Column(String(255))Profiler.init("large_resultsets",num=500000)@Profiler.setup_oncedefsetup_database(dburl,echo,num):globalengineengine=create_engine(dburl,echo=echo)Base.metadata.drop_all(engine)Base.metadata.create_all(engine)s=Session(engine)forchunkinrange(0,num,10000):s.execute(Customer.__table__.insert(),params=[{"name":"customer name %d"%i,"description":"customer description %d"%i,}foriinrange(chunk,chunk+10000)],)s.commit()@Profiler.profiledeftest_orm_full_objects_list(n):"""Load fully tracked ORM objects into one big list()."""sess=Session(engine)list(sess.query(Customer).limit(n))@Profiler.profiledeftest_orm_full_objects_chunks(n):"""Load fully tracked ORM objects a chunk at a time using yield_per()."""sess=Session(engine)forobjinsess.query(Customer).yield_per(1000).limit(n):pass@Profiler.profiledeftest_orm_bundles(n):"""Load lightweight "bundle" objects using the ORM."""sess=Session(engine)bundle=Bundle("customer",Customer.id,Customer.name,Customer.description)forrowinsess.query(bundle).yield_per(10000).limit(n):pass@Profiler.profiledeftest_orm_columns(n):"""Load individual columns into named tuples using the ORM."""sess=Session(engine)forrowin(sess.query(Customer.id,Customer.name,Customer.description).yield_per(10000).limit(n)):pass@Profiler.profiledeftest_core_fetchall(n):"""Load Core result rows using fetchall."""withengine.connect()asconn:result=conn.execute(Customer.__table__.select().limit(n)).fetchall()forrowinresult:row.id,row.name,row.description@Profiler.profiledeftest_core_fetchall_mapping(n):"""Load Core result rows using fetchall."""withengine.connect()asconn:result=(conn.execute(Customer.__table__.select().limit(n)).mappings().fetchall())forrowinresult:row["id"],row["name"],row["description"]@Profiler.profiledeftest_core_fetchmany_w_streaming(n):"""Load Core result rows using fetchmany/streaming."""withengine.connect()asconn:result=conn.execution_options(stream_results=True).execute(Customer.__table__.select().limit(n))whileTrue:chunk=result.fetchmany(10000)ifnotchunk:breakforrowinchunk:row.id,row.name,row.description@Profiler.profiledeftest_core_fetchmany(n):"""Load Core result rows using Core / fetchmany."""withengine.connect()asconn:result=conn.execute(Customer.__table__.select().limit(n))whileTrue:chunk=result.fetchmany(10000)ifnotchunk:breakforrowinchunk:row.id,row.name,row.description@Profiler.profiledeftest_dbapi_fetchall_plus_append_objects(n):"""Load rows using DBAPI fetchall(), generate an object for each row."""_test_dbapi_raw(n,True)@Profiler.profiledeftest_dbapi_fetchall_no_object(n):"""Load rows using DBAPI fetchall(), don't make any objects."""_test_dbapi_raw(n,False)def_test_dbapi_raw(n,make_objects):compiled=(Customer.__table__.select().limit(n).compile(dialect=engine.dialect,compile_kwargs={"literal_binds":True}))ifmake_objects:# because if you're going to roll your own, you're probably# going to do this, so see how this pushes you right back into# ORM land anyway :)classSimpleCustomer:def__init__(self,id_,name,description):self.id_=id_self.name=nameself.description=descriptionsql=str(compiled)conn=engine.raw_connection()cursor=conn.cursor()cursor.execute(sql)ifmake_objects:forrowincursor.fetchall():# ensure that we fully fetch!SimpleCustomer(id_=row[0],name=row[1],description=row[2])else:forrowincursor.fetchall():# ensure that we fully fetch!row[0],row[1],row[2]conn.close()if__name__=="__main__":Profiler.main()