MS SQL Server/ODBC package for Python

[Peter Herndon]

Marc-Andre, I apologize for knocking against something that is part of
your livelihood, I hadn't thought about that aspect before I posted. 
In my experience though, adodbapi was much faster.  However, I have
done no benchmarks, my situation is likely pathological, and I
wouldn't be able to build objective benchmarks anyway, so take it with
a huge grain of salt.

For what it is worth, my experience is as follows:  Using a PIII
550MHz, 256MB RAM, running WinNT 4.0 and Python 2.3.4 and connecting
to a Sybase Adaptive Server Anywhere 8.0 database, mx.ODBC took
approximately 8 wall-clock seconds to connect, while adodbapi took
about 2 seconds to connect.  That timing is a guesstimate on my part,
based on me counting.  The only thing changed in the script was the
driver import and the connect string.  Likewise, a query that would
need to search a table with a full data scan on the columns in the
where clause (vendor doesn't believe in indexes), where the table has
20k - 30k rows, takes 30-60 seconds using mx.ODBC versus 15-30 seconds
using adodbapi.

A different application, connecting to MSSQL2K, also exhibited similar
tendencies.  mx.ODBC took a bit longer to connect than adodbapi,
though the differences were *much* smaller.  Not measurable by
guesstimation, but a tad longer.

Again, this is just my experience, and my situation is not likely to
match others'.  Given other circumstances, I would expect the results
to differ, and I encourage the original poster to test all possible
solutions to find the one that best fits his needs.

---Peter Herndon

On 4/15/05, M.-A. Lemburg <mal at egenix.com> wrote:
> Peter Herndon wrote:
> > Another option is adodbapi, which in my experience is much faster than
> > mx.ODBC.
> 
> Much faster ?
> 
> See http://www.microsoft.com/technet/prodtechnol/windows2000serv/technologies/iis/reskit/iischp7.mspx
> and scroll down to Table 7.1:
> 
>       Table 7.1 TPS (transactions per second) Per Number of Threads by MDAC Technology
>       Threads   1       2       5       10      20      50
>       ODBC      66.37   146.28  350.46  626.76  900.24  859.91
>       OLEDB     67.30   141.92  326.19  590.57  794.91  715.78
>       OLEDB 2.0 61.73   126.93  297.29  506.75  575.35  526.61
>       ADO 2.0   51.24   108.12  240.91  377.30  361.26  310.34
> 
> > You can find it at http://adodbapi.sourceforge.net , and it
> > is Windows-only.  There's also http://pymssql.sourceforge.net, which is
> > cross-platform using FreeTDS and unixodbc on *nix.  I haven't any
> > experience with it, though.
> 
> --
> Marc-Andre Lemburg
> eGenix.com
> 
> Professional Python Services directly from the Source  (#1, Apr 15 2005)
> >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
> >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
> >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
> ________________________________________________________________________
> 
> ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
>