Thread: [SQLObject] Counting too often
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Frank B. <fb...@fo...> - 2003-11-03 16:52:01
|
Hallo, while tuning an application, I found that a "count(*)" takes rather long. This in itself would be okay, but somehow SQLObject counts two times, where one should be enough. The code in question is this: # Normal is my SQLObject # self._q.sql holds the current query, for example: # (normal.title LIKE '%beat%') allResults = len(Normal.select(self._q.sql)) print "--- after first count" ps = list(Normal.select(self._q.sql)[0,10]) print "--- after second count" # do somthing with "p in ps" here... This results in the following queries by SO: QueryOne: SELECT COUNT(*) FROM normal WHERE (normal.artist LIKE '%beat%') COUNT results: 163 --- after first count QueryOne: SELECT COUNT(*) FROM normal WHERE (normal.artist LIKE '%beat%') COUNT results: 163 Select: SELECT normal.id, normal.description, ... FROM normal WHERE (normal.artist LIKE '%beat%') LIMIT 10 --- after second count I'd expect that "len(Normal.select(self._q.sql)" will do a count(*), but why the second one? I tried to force a single limit-query by using list(). How could I get at the count of all results without doing several SQLs for each result? ciao -- Frank Barknecht _ ______footils.org__ |
From: Ian B. <ia...@co...> - 2003-11-03 18:00:01
|
On Nov 3, 2003, at 10:51 AM, Frank Barknecht wrote: > Hallo, > > while tuning an application, I found that a "count(*)" takes rather > long. This in itself would be okay, but somehow SQLObject counts two > times, where one should be enough. The code in question is this: > > # Normal is my SQLObject > # self._q.sql holds the current query, for example: > # (normal.title LIKE '%beat%') > > allResults = len(Normal.select(self._q.sql)) > print "--- after first count" > ps = list(Normal.select(self._q.sql)[0,10]) > print "--- after second count" > > # do somthing with "p in ps" here... > > This results in the following queries by SO: > > QueryOne: SELECT COUNT(*) FROM normal WHERE (normal.artist LIKE > '%beat%') > COUNT results: 163 > --- after first count > QueryOne: SELECT COUNT(*) FROM normal WHERE (normal.artist LIKE > '%beat%') > COUNT results: 163 > Select: SELECT normal.id, normal.description, ... FROM normal WHERE > (normal.artist LIKE '%beat%') LIMIT 10 > --- after second count > > I'd expect that "len(Normal.select(self._q.sql)" will do a count(*), > but why the second one? I tried to force a single limit-query by using > list(). How could I get at the count of all results without doing > several SQLs for each result? There's a wonky behavior in list() (and maybe various related methods) where it will get the length before iterating over the value, presumably so it can pre-allocate space. Drove me nuts, because it ignores any exceptions and so seems very magical. As a result I actually got rid of __len__ at some point (hmm... maybe between 0.4 and 0.5, though if so I forgot to document it), and replaced it with a .count() method so database calls wouldn't be implicitly triggered. Sadly this ruins truth-testing, though maybe that was dangerous too. Anyway, this shouldn't happen in 0.5. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
From: Frank B. <fb...@fo...> - 2003-11-03 19:01:30
|
Hallo, Ian Bicking hat gesagt: // Ian Bicking wrote: > On Nov 3, 2003, at 10:51 AM, Frank Barknecht wrote: > There's a wonky behavior in list() (and maybe various related methods) > where it will get the length before iterating over the value, > presumably so it can pre-allocate space. Drove me nuts, because it > ignores any exceptions and so seems very magical. > > As a result I actually got rid of __len__ at some point (hmm... maybe > between 0.4 and 0.5, though if so I forgot to document it), and > replaced it with a .count() method so database calls wouldn't be > implicitly triggered. Sadly this ruins truth-testing, though maybe > that was dangerous too. > > Anyway, this shouldn't happen in 0.5. Hm, I'm using current CVS, and there SQLObject.py still has a __len__, but no "count" method. I actually don't use a pure list in my code but a class called "ProductDisplay" derived from list. With some little changes I could make without inheriting from list: I only use "for x in productDisplay_instance: do things" which should be possible without ProductDisplay being a list. Would going this route help? ciao -- Frank Barknecht _ ______footils.org__ |
From: Ian B. <ia...@co...> - 2003-11-04 02:23:36
|
On Nov 3, 2003, at 1:01 PM, Frank Barknecht wrote: >> As a result I actually got rid of __len__ at some point (hmm... maybe >> between 0.4 and 0.5, though if so I forgot to document it), and >> replaced it with a .count() method so database calls wouldn't be >> implicitly triggered. Sadly this ruins truth-testing, though maybe >> that was dangerous too. >> >> Anyway, this shouldn't happen in 0.5. > > Hm, I'm using current CVS, and there SQLObject.py still has a __len__, > but no "count" method. I actually don't use a pure list in my code > but a class called "ProductDisplay" derived from list. With some > little changes I could make without inheriting from list: I only use > "for x in productDisplay_instance: do things" which should be possible > without ProductDisplay being a list. Would going this route help? You're right, I didn't remove __len__. But I fully intended to ;) Yes, if you just iterate over SelectResults it should work fine. Even if you just use: def makelist(v): result = [] for val in v: result.append(val) return result That should save you from excessive counts. I plan to release 0.5.1 soon, to fix a couple little bugs. I'll probably add .count() in there too. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |