Skip to content

Commit 2c081f9

Browse files
zzzeekzzzeek
committed
- large resultsets
1 parent 07d061a commit 2c081f9

4 files changed

Lines changed: 337 additions & 20 deletions

File tree

‎examples/performance/__init__.py‎

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -44,12 +44,12 @@
4444
import time
4545

4646

47-
4847
class Profiler(object):
4948
tests = []
5049

51-
def __init__(self, setup, options):
50+
def __init__(self, options, setup=None, setup_once=None):
5251
self.setup = setup
52+
self.setup_once = setup_once
5353
self.test = options.test
5454
self.dburl = options.dburl
5555
self.runsnake = options.runsnake
@@ -72,6 +72,9 @@ def run(self):
7272
else:
7373
tests = self.tests
7474

75+
if self.setup_once:
76+
print("Running setup once...")
77+
self.setup_once(self.dburl, self.echo, self.num)
7578
print("Tests to run: %s" % ", ".join([t.__name__ for t in tests]))
7679
for test in tests:
7780
self._run_test(test)
@@ -100,14 +103,15 @@ def _run_with_time(self, fn):
100103
self.stats.append(TestResult(self, fn, total_time=total))
101104

102105
def _run_test(self, fn):
103-
self.setup(self.dburl, self.echo)
106+
if self.setup:
107+
self.setup(self.dburl, self.echo, self.num)
104108
if self.profile or self.runsnake or self.dump:
105109
self._run_with_profile(fn)
106110
else:
107111
self._run_with_time(fn)
108112

109113
@classmethod
110-
def main(cls, setup):
114+
def main(cls, num, setup=None, setup_once=None):
111115
parser = argparse.ArgumentParser()
112116

113117
parser.add_argument(
@@ -119,8 +123,9 @@ def main(cls, setup):
119123
help="database URL, default sqlite:///profile.db"
120124
)
121125
parser.add_argument(
122-
'--num', type=int, default=100000,
123-
help="Number of iterations/items/etc for tests, default 100000"
126+
'--num', type=int, default=num,
127+
help="Number of iterations/items/etc for tests; "
128+
"default is %d module-specific" % num
124129
)
125130
parser.add_argument(
126131
'--profile', action='store_true',
@@ -133,13 +138,12 @@ def main(cls, setup):
133138
help='invoke runsnakerun (implies --profile)')
134139
parser.add_argument(
135140
'--echo', action='store_true',
136-
help="Echo SQL output"
137-
)
141+
help="Echo SQL output")
138142
args = parser.parse_args()
139143

140144
args.profile = args.profile or args.dump or args.runsnake
141145

142-
Profiler(setup, args).run()
146+
Profiler(args, setup=setup, setup_once=setup_once).run()
143147

144148

145149
class TestResult(object):

‎examples/performance/bulk_inserts.py‎

Lines changed: 24 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
from . import Profiler
22

33
from sqlalchemy.ext.declarative import declarative_base
4-
from sqlalchemy import Column, Integer, String, create_engine
4+
from sqlalchemy import Column, Integer, String, create_engine, bindparam
55
from sqlalchemy.orm import Session
66

77
Base = declarative_base()
@@ -15,7 +15,7 @@ class Customer(Base):
1515
description = Column(String(255))
1616

1717

18-
def setup_database(dburl, echo):
18+
def setup_database(dburl, echo, num):
1919
global engine
2020
engine = create_engine(dburl, echo=echo)
2121
Base.metadata.drop_all(engine)
@@ -111,22 +111,35 @@ def test_core_insert(n):
111111

112112

113113
@Profiler.profile
114-
def test_sqlite_raw(n):
115-
"""pysqlite's pure C API inserting rows in bulk, no pure Python at all"""
116-
conn = engine.raw_connection()
114+
def test_dbapi_raw(n):
115+
"""The DBAPI's pure C API inserting rows in bulk, no pure Python at all"""
116+
117+
conn = engine.pool._creator()
117118
cursor = conn.cursor()
118-
cursor.executemany(
119-
"INSERT INTO customer (name, description) VALUES(:name, :description)",
120-
[
119+
compiled = Customer.__table__.insert().values(
120+
name=bindparam('name'),
121+
description=bindparam('description')).\
122+
compile(dialect=engine.dialect)
123+
124+
if compiled.positional:
125+
args = (
126+
('customer name %d' % i, 'customer description %d' % i)
127+
for i in range(n))
128+
else:
129+
args = (
121130
dict(
122131
name='customer name %d' % i,
123132
description='customer description %d' % i
124133
)
125134
for i in range(n)
126-
]
135+
)
136+
137+
cursor.executemany(
138+
str(compiled),
139+
list(args)
127140
)
128141
conn.commit()
129-
142+
conn.close()
130143

131144
if __name__ == '__main__':
132-
Profiler.main(setup=setup_database)
145+
Profiler.main(setup=setup_database, num=100000)

‎examples/performance/large_resultsets.py‎

Lines changed: 155 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,155 @@
1+
"""In this series of tests, we are looking at time to load 1M very small
2+
and simple rows.
3+
4+
"""
5+
from . import Profiler
6+
7+
from sqlalchemy.ext.declarative import declarative_base
8+
from sqlalchemy import Column, Integer, String, create_engine, literal_column
9+
from sqlalchemy.orm import Session, Bundle
10+
11+
Base = declarative_base()
12+
engine = None
13+
14+
15+
class Customer(Base):
16+
__tablename__ = "customer"
17+
id = Column(Integer, primary_key=True)
18+
name = Column(String(255))
19+
description = Column(String(255))
20+
21+
22+
def setup_database(dburl, echo, num):
23+
global engine
24+
engine = create_engine(dburl, echo=echo)
25+
Base.metadata.drop_all(engine)
26+
Base.metadata.create_all(engine)
27+
28+
s = Session(engine)
29+
for chunk in range(0, num, 10000):
30+
s.bulk_insert_mappings(Customer, [
31+
{
32+
'name': 'customer name %d' % i,
33+
'description': 'customer description %d' % i
34+
} for i in range(chunk, chunk + 10000)
35+
])
36+
s.commit()
37+
38+
39+
@Profiler.profile
40+
def test_orm_full_objects(n):
41+
"""Load fully tracked objects using the ORM."""
42+
43+
sess = Session(engine)
44+
# avoid using all() so that we don't have the overhead of building
45+
# a large list of full objects in memory
46+
for obj in sess.query(Customer).yield_per(1000).limit(n):
47+
pass
48+
49+
50+
@Profiler.profile
51+
def test_orm_bundles(n):
52+
"""Load lightweight "bundle" objects using the ORM."""
53+
54+
sess = Session(engine)
55+
bundle = Bundle('customer',
56+
Customer.id, Customer.name, Customer.description)
57+
for row in sess.query(bundle).yield_per(10000).limit(n):
58+
pass
59+
60+
61+
@Profiler.profile
62+
def test_orm_columns(n):
63+
"""Load individual columns into named tuples using the ORM."""
64+
65+
sess = Session(engine)
66+
for row in sess.query(
67+
Customer.id, Customer.name,
68+
Customer.description).yield_per(10000).limit(n):
69+
pass
70+
71+
72+
@Profiler.profile
73+
def test_core_fetchall(n):
74+
"""Load Core result rows using Core / fetchall."""
75+
76+
with engine.connect() as conn:
77+
result = conn.execute(Customer.__table__.select().limit(n)).fetchall()
78+
for row in result:
79+
data = row['id'], row['name'], row['description']
80+
81+
82+
@Profiler.profile
83+
def test_core_fetchchunks_w_streaming(n):
84+
"""Load Core result rows using Core with fetchmany and
85+
streaming results."""
86+
87+
with engine.connect() as conn:
88+
result = conn.execution_options(stream_results=True).\
89+
execute(Customer.__table__.select().limit(n))
90+
while True:
91+
chunk = result.fetchmany(10000)
92+
if not chunk:
93+
break
94+
for row in chunk:
95+
data = row['id'], row['name'], row['description']
96+
97+
98+
@Profiler.profile
99+
def test_core_fetchchunks(n):
100+
"""Load Core result rows using Core / fetchmany."""
101+
102+
with engine.connect() as conn:
103+
result = conn.execute(Customer.__table__.select().limit(n))
104+
while True:
105+
chunk = result.fetchmany(10000)
106+
if not chunk:
107+
break
108+
for row in chunk:
109+
data = row['id'], row['name'], row['description']
110+
111+
112+
@Profiler.profile
113+
def test_dbapi_fetchall(n):
114+
"""Load DBAPI cursor rows using fetchall()"""
115+
116+
_test_dbapi_raw(n, True)
117+
118+
119+
@Profiler.profile
120+
def test_dbapi_fetchchunks(n):
121+
"""Load DBAPI cursor rows using fetchmany()
122+
(usually doesn't limit memory)"""
123+
124+
_test_dbapi_raw(n, False)
125+
126+
127+
def _test_dbapi_raw(n, fetchall):
128+
compiled = Customer.__table__.select().limit(n).\
129+
compile(
130+
dialect=engine.dialect,
131+
compile_kwargs={"literal_binds": True})
132+
133+
sql = str(compiled)
134+
135+
import pdb
136+
pdb.set_trace()
137+
conn = engine.raw_connection()
138+
cursor = conn.cursor()
139+
cursor.execute(sql)
140+
141+
if fetchall:
142+
for row in cursor.fetchall():
143+
# ensure that we fully fetch!
144+
data = row[0], row[1], row[2]
145+
else:
146+
while True:
147+
chunk = cursor.fetchmany(10000)
148+
if not chunk:
149+
break
150+
for row in chunk:
151+
data = row[0], row[1], row[2]
152+
conn.close()
153+
154+
if __name__ == '__main__':
155+
Profiler.main(setup_once=setup_database, num=1000000)

0 commit comments

Comments
 (0)