fun with ADO

[Larry]

I thought I'd share some stuff I found while trying to tune an app
that interacts heavily with a sql server database via ADO. The web
application I've been rewriting in python was having serious problems
with performance. I knew it wasn't IIS because things were running
really slow from the interactive window too.

After fooling around with profile for a while the pig was unveiled as
actually getting the field values from the Sql Server database via
win32com/ADO.

a toy version of the technique I was using was:

    rs=win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(sql,connectString)
    while not rs.EOF:
        name=str(rs.Fields("clientname").Value)  #oink!
        gid=str(rs.Fields("gid").Value)
        (- do something fun with fields -)
        rs.MoveNext()
    rs.Close()

A put some timers on a test script that ran through a few hundred
records, and it ran in about 1.9 seconds. That sucks. I also tried
using mapping field values into a list and extracting from that, no
better.

So I tried removing the Value, as in: name=str(rs("clientname"))
The average new run time was 0.7 seconds! Woah. Nice.

Next I tried using the sparsely documented Collect method of the
recordset object, as in: name=str(rs.Collect("clientname"))
The average run time was now down to 0.24 seconds. Viva Collect!

Finally I ran across an article where someone mentioned this
technique:

    rs.Open(sql,connectString)
    f1=rs("gid")
    f2=rs("clientname")
    while not rs.EOF:
        name=str(f2)
        gid=str(f1)
        (- do something fun with fields -)
        rs.MoveNext()
    rs.Close()

We have a winner! Average time was 0.18 seconds for this method.

So for any "ADOites" (tm) out there in python land I'd recommend
either Collect or maintaining field references outside of recordset
iterations. I've increased performance in many parts of my application
by 10x or more.