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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---