47
47
... 'Cupid', 'Donner', 'Blitzen', 'Rudolph']
50
Performance with SQLObject
51
==========================
53
This section demonstrates that batching generates sensible SQL queries when
54
used with SQLObject, i.e. that it puts appropriate LIMIT clauses on queries.
56
Imports and initialization:
58
>>> from lp.testing.pgsql import CursorWrapper
59
>>> from canonical.launchpad.database.emailaddress import EmailAddress
60
>>> from canonical.launchpad.interfaces.lpstorm import IStore
61
>>> ignore = IStore(EmailAddress) # Prime the database connection.
62
... # (Priming is important if this test is run in isolation).
63
>>> CursorWrapper.record_sql = True
65
Prepare a query, and create a batch of the results:
67
>>> select_results = EmailAddress.select(orderBy='id')
68
>>> batch_nav = BatchNavigator(select_results, build_request(), size=10)
69
>>> email_batch = batch_nav.currentBatch()
70
>>> batch_items = list(email_batch)
72
Because we're only looking at the first batch, the database is only
73
asked for the first 11 rows. (lazr.batchnavigator asks for 11 instead
74
of 10 so that it can reliably detect the end of the dataset).
76
>>> len(CursorWrapper.last_executed_sql)
78
>>> print CursorWrapper.last_executed_sql[0]
79
SELECT ... FROM EmailAddress ... LIMIT 11...
81
Get the next 10. The database is only asked for the next 11 rows:
83
>>> CursorWrapper.last_executed_sql = []
84
>>> email_batch2 = email_batch.nextBatch()
85
>>> batch_items = list(email_batch2)
86
>>> len(CursorWrapper.last_executed_sql)
88
>>> CursorWrapper.last_executed_sql[0].endswith('LIMIT 11 OFFSET 10')
91
As seen above, simply accessing the batch doesn't trigger a SQL query
92
asking for the length of the entire resultset. But explicitly asking
93
for the length will trigger a SQL query in most circumstances.
95
>>> CursorWrapper.last_executed_sql = []
96
>>> ignored = email_batch.total()
97
>>> print CursorWrapper.last_executed_sql[0]
98
SELECT COUNT(*) FROM EmailAddress
100
There are exceptions. When the current batch is the last one in the
101
list, it's possible to get the length of the entire resultset without
102
triggering a COUNT query.
104
>>> CursorWrapper.last_executed_sql = []
105
>>> batch_nav = BatchNavigator(select_results, build_request(), size=50)
106
>>> final_batch = batch_nav.currentBatch().nextBatch()
107
>>> batch_items = list(final_batch)
108
>>> ignored = final_batch.total()
109
>>> print "\n".join(CursorWrapper.last_executed_sql)
110
SELECT ... FROM EmailAddress ... OFFSET 0
111
SELECT ... FROM EmailAddress ... OFFSET 50
113
When the current batch contains the entire resultset, it's possible to
114
get the length of the resultset without triggering a COUNT query.
116
>>> CursorWrapper.last_executed_sql = []
117
>>> one_page_nav = BatchNavigator(select_results, build_request(), size=200)
118
>>> only_batch = one_page_nav.currentBatch()
119
>>> batch_items = list(only_batch)
120
>>> ignored = only_batch.total()
121
>>> print "\n".join(CursorWrapper.last_executed_sql)
122
SELECT ... FROM EmailAddress ... OFFSET 0
53
127
The batch navigator tells us whether multiple pages will be used.
55
>>> from canonical.launchpad.database.emailaddress import EmailAddress
56
>>> select_results = EmailAddress.select(orderBy='id')
57
>>> batch_nav = BatchNavigator(select_results, build_request(), size=50)
58
129
>>> batch_nav.has_multiple_pages
61
>>> one_page_nav = BatchNavigator(select_results, build_request(), size=200)
62
132
>>> one_page_nav.has_multiple_pages
added
removed
lib/lp/app/doc/batch-navigation.txt
