Thread: [SQLObject] SQL'ish count versus len()
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Michael G. <mai...@ga...> - 2006-06-27 06:39:39
|
Hi, thank your for the reply to me previous posting. Using SQLMultipleJoin (instead of MultipleJoin) speeds things up dramatically. Again a question on a similar issue: I need to count the elements. I would like to do this fast and elegant. class Job(sqlobject.SQLObject): name = sqlobject.StringCol() ... transactions = sqlobject.SQLMultipleJoin('Transaction') To count the transactions of a job I can do: 1) numtrs = Transaction.select(Job.q.id==int(id)).count() (SQL'ish, This lets the DB do the counting) 2) numtrs = len([a.id for a in page.job.transactions]) (Pythonic, This loads all data and counts it) Where 1) is fast and 2) is elegant. Is there a fast and elegant way? Thanks again for suggestions. Regards, Michael Gauckler |
From: Lutz S. <l.s...@4c...> - 2006-06-27 08:20:43
|
Hello, On Tue, 27 Jun 2006 06:39:27 +0000 (UTC) Michael Gauckler <mai...@ga...> wrote: > Hi, > > thank your for the reply to me previous posting. Using SQLMultipleJoin > (instead of MultipleJoin) speeds things up dramatically. have I missed something ? I've not seen the reply on the list. Kindly regards Lutz Steinborn |
From: Michael G. <mai...@ga...> - 2006-06-27 11:46:07
|
> have I missed something ? > I've not seen the reply on the list. The reply went to my private address, see below. -------- Forwarded Message -------- From: Luke Opperman <lu...@me...> To: Michael Gauckler <mai...@ga...> Subject: Re: [SQLObject] Reuducing the number of SQL Queries for MultipleJoins Date: Mon, 26 Jun 2006 19:14:02 -0500 I haven't looked closely at it, and it seems your debug sql statements don't quite match the code you've posted, but i believe if you switch the counterpartys join to be a SQLMultipleJoin it will match what you want. That will use a SelectResults for the join instead of the old join logic. Luke Quoting Michael Gauckler <mai...@ga...>: Hi, > I have an tree like object structue: class Application(sqlobject.SQLObject): jobs = sqlobject.MultipleJoin('Job') class Job(sqlobject.SQLObject): counterpartys = sqlobject.MultipleJoin('Counterparty') class Counterparty(sqlobject.SQLObject): name = sqlobject.StringCol() There are two ways in which I can access the Counterpartys belonging to job 3 of application 0: 1) Traversing the tree: app = Application.select()[0] job = app.jobs[3] [cp for cp in job.counterpartys] 2) Creating a query [cp for cp in Counterparty.select(Job.q.id == 4 and Application.q.id == 1)] In case 1) a SQL query for the job and then another one for each of the counterpartys is created. Since there are many counterparties this yields a huge number of SQL queries and things get very slow: 1/QueryAll: SELECT id FROM counterparty WHERE job_id = 4 1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM counterparty WHERE id = 1 1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM counterparty WHERE id = 2 1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM counterparty WHERE id = 3 ... In case 2) this will yield a single SQL query 1/Select : SELECT counterparty.id, counterparty.name, counterparty.rating, counterparty.global_id, counterparty.cif, counterparty.job_id, counterparty.nogas, counterparty.sectors FROM application, counterparty WHERE (application.id = 1) I would like to use the syntax in 1), since the goal of using SQLObject is an abstraction away from SQL statements towards objects, but with the performance of 2). Is there a way to achieve this, or an explanation why SQLObject cannot optimize this? Any help is appreciated. Thank you! Yours, Michael Gauckler |
From: Luke O. <lu...@me...> - 2006-06-27 11:46:30
|
Forgot to copy the list before, gist was use SQL*Join instead, so that it uses a SelectResults instance. That's also the answer here: you can then use .count, .filter, .sum, etc on the join property. j.transactions.count() Quoting Michael Gauckler <mai...@ga...>: > Hi, > > thank your for the reply to me previous posting. Using SQLMultipleJoin > (instead of MultipleJoin) speeds things up dramatically. > > Again a question on a similar issue: I need to count the elements. I would > like to do this fast and elegant. > > class Job(sqlobject.SQLObject): > name = sqlobject.StringCol() > ... > transactions = sqlobject.SQLMultipleJoin('Transaction') > > To count the transactions of a job I can do: > > 1) > numtrs = Transaction.select(Job.q.id==int(id)).count() > (SQL'ish, This lets the DB do the counting) > > 2) > numtrs = len([a.id for a in page.job.transactions]) > (Pythonic, This loads all data and counts it) > > Where 1) is fast and 2) is elegant. Is there a fast and elegant way? > > Thanks again for suggestions. > > Regards, > Michael Gauckler > > |
From: Michael G. <mai...@ga...> - 2006-06-27 11:55:09
|
Luke Opperman <luke <at> metathusalan.com> writes: > > Forgot to copy the list before, gist was use SQL*Join instead, so that it uses > a SelectResults instance. That's also the answer here: you can then use > .count, .filter, .sum, etc on the join property. > > j.transactions.count() > Thank you, this works great! - Michael |