Re: MS SQL Server/ODBC package for Python

Peter Herndon wrote: 
:) Knock away, as my info isn't scientific anyway.  In my case, ASA is
*not* local.  The db is running on a 500MHz x 2 server with 768MB RAM,
over 100BaseT connection.  That same server is also running the MSSQL
instance, and IIS.

Running your benchmark, I ran into a couple of interesting points.
Using mx.ODBC, my times were 0.54 seconds and 6.56 seconds
respectively, while using adodbapi my results are 3.55 seconds and 25.9
seconds respectively.  mx.ODBC is faster with the simple query you
provide.

Next I modified the benchmark to reflect my particular circumstances
more accurately (?Maybe?  Comments invited).  I changed the query to
one of the queries in regular use in my application.  This query
selects 26 columns from 3 joined tables, with a where clause "where
f476 = ?", and I provide the missing value as a tuple in the execute
statement.  Note that, as I mentioned in my reply to M-A, the f476
field is not indexed, and is a long varchar.  Again, the system is
bought, so I have no control over the schema. ;)

The other change I made was to reduce the number of iterations from 100
to 10.  Since there are 128000 records in the main table, the wait for
100 iterations was too long for my patience.  Under these
circumstances, mx.ODBC's numbers are 188.49 seconds and 377.56 seconds
respectively, and adodbapi's times are 111.15 seconds and 223.55
seconds respectively.


Just curious: are you timing just the time it takes to
complete the .execute() or do you also fetch the complete
result or only part of it ?

Note that we have had reports about MS SQL Server being very
slow in determining the number of rows in a result set. It's
possible that ASA has the same problem.

The mxODBC 2.0 release fetches this number after every .execute().
If adodbapi avoids this (which we'll also integrate into mxODBC 2.1),
then this would explain the differences you see.

Another reason could indeed be related to the longvarchar
field: these fields are fetched in multiple chunks if the
ODBC driver doesn't provide proper size information - each
of these chunks will require a network access which slows
down the data fetching.

Since mxODBC supports Unicode, but defaults to returning
8-bit strings, it is also possible that your longvarchar
column is sent as Unicode and has to be converted to
an 8-bit string first. Thus, allowing mxODBC to return Unicode
could make a difference as well (see the docs on how this is
done).

My first wall-clock impressions are obvious exaggerations of reality,
for which I duly apologize to all.  However, adodbapi did prove to be
faster in my admittedly very wacky common use case.  Slower to connect,
but faster to run a substantial query.

Comments? Questions? Suggestions for improvement? 

See some of the hints I mentioned above.

Note that it often also pay off checking the ODBC driver
settings, esp. if you have a networked setup - ODBC drivers
often pre-fetch result sets and changing the defaults they
use for this can make a huge difference in response times.

Unfortunately, mxODBC doesn't have control over these
settings and there's no standard for them, so you'll
have to check the ODBC driver documentation for details
on the best settings can be found and set.

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 18 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 ! ::::
.

Relevant Pages

  • Re: MS SQL Server/ODBC package for Python
    ... > The mxODBC 2.0 release fetches this number after every .execute. ... > ODBC driver doesn't provide proper size information - each ... > Since mxODBC supports Unicode, ... > settings and there's no standard for them, ...
    (comp.lang.python)
  • Re: ODBC driver manager mangles delimited column names
    ... Thanks Robert, I'm beginning to think you are right. ... I've just finished running some more tests with the trace logging turned on for the ODBC driver manager. ... This is what I get in the log for the automatically generated query: ...
    (microsoft.public.data.odbc)
  • Re: Accessing a paradox table from a C# application...
    ... SELECT TOP 1 * FROM ModelSer ORDER BY Timestamp ... How much faster is the query if you strip off the PX? ... The ODBC driver that I am using is the Microsoft Paradox ...
    (comp.databases.paradox)
  • Re: Informix+ODBC+ADO.NET - problem
    ... It is hard for me to imagine why any problem with a select query would ... the ODBC driver and your application is assuming that whatever went ... wrong was the same as a dead connection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ODBC parameter query
    ... connecting to thru the ODBC driver, and the type of connection you're ... It sounds like you're using SQL pass-thru queries, ... Then use the query. ... > I have a set of foreign tables to query through a third party ODBC driver. ...
    (microsoft.public.access.queries)