On 11/04/2011 03:11 AM, Mark Erbaugh wrote:
On Nov 3, 2011, at 3:31 PM, werner wrote:
Mark,
On 11/03/2011 07:18 PM, Mark Erbaugh wrote:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes 'select count(*) from table' using the ORM, not the SQL generator?
Just the other day I thought I needed the same, initially I just used the "id" column which all my
tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other
dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the
"id" values of that table (to build a virtual listctrl in wxPython), so instead of doing the count
and starting feeling the list I got the "id" and did a len(onresult) to get my count.
Point I am trying to make with a lot of words, maybe there is a solution which
doesn't need count() at all:-) .
I never considered that a count(*) was that expensive especially if there is no
where clause.
I think it depends a lot on the SQL one uses. I know it is recommended
not to use count on Firebird SQL unless there is no other solution.
Just did a google search on "count() sql slow" and there seem to be
problems with other db engines too.
I would think that it would be less expensive than actually retrieving all
the rows and counting them.
Just did a little test with one of my larger tables (over 16,000 rows)
and doing:
qTable = db.Cepagesyn
idCol = qTable.id
resultT = session.query(qTable).all() # about 1 sec
resultC = session.query(idCol).all() # about .25 sec
count = session.query(db.sa.func.count(idCol)).all() # about .01 sec
So, you are absolutely right count() is faster. In my case I need
resultC any way, so doing count and then sometimes later resultC could
return a different number and I really don't need it.
What if there are millions of rows? The result set could fill up memory. In
my case, I just need to know how many rows.
Don't deal in millions (Euros or rows) :-) .
I don't care about any other details. In one case, I'm checking to see if there
are zero rows, in which case, I populate the table with initial rows. In
another case, I'm just unittesting some code and I want to make sure that there
are the proper number of rows in the table as one of the test conditions.
I believe the "count" test line is what you were looking for (btw
db.Cepagesyn is the class of my table "cepagesyn").
Werner
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
