Re: [SQLObject] Counting too often

[Ian B. <ia...@co...>]

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