fun with ADO

[logistix]

Stupid question, but did you run MakePy against the ADO objects?  Seems like
the time saved by dropping the Value reference could have to do with
Dispatch name resolution.

Just curious.

--
-

"Larry" <wrbt at email.com> wrote in message
news:2ec1bc1c.0202221202.1e0ea6aa at posting.google.com...
> 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.