Hi,
I'm in a particular situation where our ORM setup is taking way too
long to perform a selection from a large data set.
In my database I have 5000 customers who made purchases and made some
form of payment. I need to find the names of all customers who made
payments by cash.
My SQL query looks like this:
SELECT customers.name, payments.payid
FROM customers, purchases, payments
WHERE customers.cid = purchases.cid
AND purchases.payid = payments.payid
AND payments.pay_type = 'cash'
Currently my code looks like this:
customers = Customer.select()
for customer in customers:
for purchase in customer.purchases:
if purchase.payment.pay_type == 'cash':
print "%s, %s" % (customer.name, purchase.payment.payid)
Customer is a class with a mapper assigned to it using assign_mapper()
(please see the source[1] for details).
In my file (below) I perform the query three ways: using mappers, using
a statement which I assemble manually, and using a MySQL query. The
timings are as follows:
$ time python dbprofile.py mapper > /dev/null
real 0m19.256s
user 0m11.501s
sys 0m5.416s
$ time python dbprofile.py "raw sql" > /dev/null
real 0m0.186s
user 0m0.128s
sys 0m0.044s
$ time python dbprofile.py statement > /dev/null
real 0m0.346s
user 0m0.244s
sys 0m0.084s
What's the best way to execute this query fast? I have read the
"Working with Large Collections" section of the documentation, but it
seems to deal mostly with deletion.
Is there a custom mapper I could define which would select the data I
need in one DB query?
[1] Source here: http://rafb.net/p/vaELsx86.html
Thanks!
Mike
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
