[egenix-users] cursor.rowcount abnormalities and performance question

[Jim Vickroy]

Regarding the use of fecthone() vs fetchall(), my advice is the standard
Python mantra -- avoid premature optimization -- code it in a simple and clear
manner -- test it in you problem domain (expected number of rows, size per
row, etc.) -- optimize when/where testing reveals performance issues.  I have
been pleasantly surprised how fast SQL Server retrievals are for 1mb BLOBs.

stuff at mailzilla.net wrote:

> Hello,
>
> When using mxODBC with the Windows SQL Server Driver I've noticed that
> when performing a query with executedirect the cursor object has a
> rowcount of -1 or 0 and nothing else.  I am using Python 2.2 and the
> mxODBC package that ships with BlackAdder.
>
> -1 seems to be the initial value of rowcount before queries are performed
> but I'm unsure as to why this would remain the value after a query
> considering that the count should be >= 0, at least fundamentally.  In my
> tests I have only seen values of 0 (when there are no records) and -1 when
> there are some records-- in these cases, I am expecting this to be a
> positive value representing the number of records that matched the query.
>
> If I do a fetch operation (i.e. fetchone) on the cursor that has a
> rowcount of -1 I do indeed retrieve records.  There seems to be a
> disconnect between rowcount and being able to fetch records.
>
> Is SQL Server/ODBC sending over misleading info or is this a problem with
> mxODBC?
>
> I generally use rowcount to determine whether I need to retreive
> records using fetchone inside a while loop (no need to execute the loop
> if rowcount is 0).  It's a little cleaner if I can reliably use rowcount
> but if the value doesn't sync with the actual result set then I can safely
> do something like this:
>
> while 1:
>    row = c.fetchone()
>    if not row: break
>    processTheRecord(row)
>
> If rowcount can be relied upon, I could do this, which is much cleaner:
>
> for i in range(c.rowcount):
>    row = c.fetchone
>    processTheRecord(row)
>
> Performance question- Since I need to perform some manipulation on each
> record I believe it is more efficient to perform a while loop and
> fetchone rather than getting everything via fetchall and then iterating
> over that result (since theoretically, fetchall would be iterating to get
> each record so technically speaking that would require iterating twice).
> If this assumption is false, please let me know.  Perhaps there are some
> internal ODBC/mxODBC optimizations that allow fetchall to actually perform
> much faster than several fetchone's.  My concern is that if fetchall
> iterates through the result set to produce the list of rows and then I am
> iterating through the fetched list then I can accomplish the same goal w/
> 1 less iteration via a series of fetchone's.  Also, I imagine it would be
> safer to process each record individually then fetching the entire result
> set at once in terms of system resource utilization (i.e. if the result
> set were millions of records there would need to be data structs allocated
> to hold them).
>
> I'm curious to see if my reluctance to use fetchall is well-founded or
> overly paranoid.
>
> Thanks for any info,
>
> Phil
>
> _______________________________________________________________________
> eGenix.com User Mailing List                     http://www.egenix.com/
> http://lists.egenix.com/mailman/listinfo/egenix-users