Re: MS SQL Server/ODBC package for Python

[Peter Herndon]

> > 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 ?
> 

I have included the .fetchall() in the loop, yes.  The only other
change I made to the benchmark is to use ODBC.Windows.DriverConnect
rather than .Connect, as that's what I'd used before in my
application.  Is there a difference between the two, other than the
argument syntax?

> 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.
> 

I look forward to retesting with 2.1 -- I'm curious to see what
differences arise.

> 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.

The datum in the field is uniformly short, 8 characters or less.  It's
actually our internal identifier.  Again, I'd use a varchar and index
the field if I had control over the schema.  However, the small size
of the datum argues against multiple chunks, unless the default chunk
size is really small.

> 
> 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).

I'll take a look into this, as I have a vague memory of adodbapi
returning Unicode by default.

> 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.

I'll have to dig into this.  There don't seem to be too many directly
available settings in ODBC Administrator, but there seem to be some
spots for inserting settings directly into a command line, so I'll
need to dig up the Sybase documentation on what's possible.

> > Regards,
> --
> Marc-Andre Lemburg
> eGenix.com

Thank you very much for your patience and insight.

Cheers,

---Peter Herndon
--
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[M.-A. Lemburg]

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 ! 
--
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Francois Lepoutre]

Hi Peter
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.
We agree on figures at this stage :)
Next I modified the benchmark to reflect my particular circumstances
more accurately [...] 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.
This is an interesting feedback. It looks like both middleware have
their distinct value and distinct set of advantages.
I'll definitely review my judgment on ADO!
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?
Based on your results, my feeling is that mx.ODBC remains a solution
of choice for db-support behing web services "à la mod_python"
where connection time is essential whilst adodbapi would be the
definite winner when it comes to typical db-intensive win32-based
applications (such as wxpython-based ones).
Regards to you
Francois
--
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Peter Herndon]

I switched around the order, both in the actual application and in my
tests as replied to Francois Lepoutre above.  Results were consistent,
after the first run of any given test, which unsurprisingly took a bit
longer.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Peter Herndon]

:) 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.

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?

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Francois Lepoutre]

Peter,
May my I apologize for knocking against your information, as well.
> 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
As a long time user of the ASA range of products, I was surprised
by your connection time to ASA 8. I'm not a regular user of mxODBC
but i have tested it with success here. With no pending time upon
connection whatever the middleware.
The script below ran fast on my machine (an oldish pentium 400)
with ASA 8.0.2 (the engine is local).
When re-cycling ASA connection (a good practice) the script took
0.21 sec. to run and  3.4 sec. when re-building the connection
on every hit (which you should avoid).
For 100 connections, not one! Which confirms my feeling that
something got in the way when you ran your test.
mxODBC is fast and safe (on both linux and win32).
I won't comment on ado since i'm not a user. But the fact that
ASA+mxODBC runs multi-platform may be an additional advantage.
Regards
Francis
from mx import ODBC
import time
mytime=time.time()
print "1 connection and 750 cursors started, used and closed at once..."
dbHandle=ODBC.Windows.Connect("descriptive demo fr","dupont","",0)
for i in range(100):
cHandle=dbHandle.cursor()
cHandle.execute("select 1")
cHandle.fetchall()
cHandle.close()
print time.time() - mytime
print "750 connection fully started, used and closed at once..."
for i in range(100):
dbHandle=ODBC.Windows.Connect("descriptive demo fr","dupont","",0)
cHandle=dbHandle.cursor()
cHandle.execute("select 1")
cHandle.fetchall()
cHandle.close()
dbHandle.close()
print time.time() - mytime
--
http://mail.python.org/mailman/listinfo/python-list

Re: 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 <[EMAIL PROTECTED]> 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 ! 
>
--
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[M.-A. Lemburg]

[EMAIL PROTECTED] wrote:
> I do have a standard install of Python as well as Plone.  Can you have two?
> I have run the mxInstaller for both instances  however, whether I run
> the ide from my standard install or my plone install the pythonpath is the
> same (ie c:\...\plone 2\python).  My machine registry now has the python
> entries all pointing at plone.
> 
> Sorry to trouble you like this but do you know how I can get myself out of
> this mess?

For Plone, you need to install the "mxODBC Zope DA", not
stand-alone mxODBC Python extension:

http://www.egenix.com/files/python/mxODBC-Zope-DA.html

Installation docs are here:

http://www.egenix.com/files/python/mxODBC-Zope-DA.html#Documentation

-- 
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 ! 
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[M.-A. Lemburg]

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 ! 
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Peter Herndon]

Another option is adodbapi, which in my experience is much faster than
mx.ODBC.  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.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[gsmith]

I do have a standard install of Python as well as Plone.  Can you have two?
I have run the mxInstaller for both instances  however, whether I run
the ide from my standard install or my plone install the pythonpath is the
same (ie c:\...\plone 2\python).  My machine registry now has the python
entries all pointing at plone.

Sorry to trouble you like this but do you know how I can get myself out of
this mess?

Cheers.

graham







   
  Simon Brunning
   
  <[EMAIL PROTECTED]To:   Graham <[EMAIL 
PROTECTED]>  
  mail.com>cc:   python-list@python.org 
   
   Subject:  Re: MS SQL Server/ODBC 
package for Python 
  15/04/2005 14:21  
   
  Please respond to 
   
  simon 
   
   :3 Kb
   

   

   




On 4/15/05, Graham <[EMAIL PROTECTED]> wrote:
> Thanks Simon
> I have just installed mxODBC and tried one of their samples.
> Am getting an error on trying to import mx.ODBC
>
> ImportError: No module named mx.ODBC
>
> After the install the mx folder has been place in my C:\Program
Files\Plone
> 2\Python\lib\site-packages folder however when I browse the PythonPath
and
> look under Standard Python Library I can't see mx.

Hmmm. Have you got more than one instance of Python installed? Do you
have a 'standard' installation in addition to Plone's? If so, perhaps
mxODBC has been installed for the wrong one.

--
Cheers,
Simon B,
[EMAIL PROTECTED],
http://www.brunningonline.net/simon/blog/








-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Simon Brunning]

On 4/15/05, Graham <[EMAIL PROTECTED]> wrote:
> Thanks Simon
> I have just installed mxODBC and tried one of their samples.
> Am getting an error on trying to import mx.ODBC
> 
> ImportError: No module named mx.ODBC
> 
> After the install the mx folder has been place in my C:\Program Files\Plone
> 2\Python\lib\site-packages folder however when I browse the PythonPath and
> look under Standard Python Library I can't see mx.

Hmmm. Have you got more than one instance of Python installed? Do you
have a 'standard' installation in addition to Plone's? If so, perhaps
mxODBC has been installed for the wrong one.

-- 
Cheers,
Simon B,
[EMAIL PROTECTED],
http://www.brunningonline.net/simon/blog/
--
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Graham]

Thanks Simon
I have just installed mxODBC and tried one of their samples.
Am getting an error on trying to import mx.ODBC

ImportError: No module named mx.ODBC

After the install the mx folder has been place in my C:\Program Files\Plone
2\Python\lib\site-packages folder however when I browse the PythonPath and
look under Standard Python Library I can't see mx.

Any ideas what I mnay have done wrong?

many thanks
Graham


"Simon Brunning" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
On 4/15/05, Graham <[EMAIL PROTECTED]> wrote:
> I need a SQL Server or ODBC package for Python.  Can anyone help please?
> Have search the Python packages under Database and there is no reference
to
> either SQL Server or ODBC.

Top of the line is probably mxODBC
(). It's the Rolls
Royce of DB-API modules. It's not free for commercial use, but it's
worth every penny/cent/groat/whatever.

For commercial work where I've not been able to justify spending money
(in my bosses opinion at least), adodbapi
() works a treat.

--
Cheers,
Simon B,
[EMAIL PROTECTED],
http://www.brunningonline.net/simon/blog/


-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MS SQL Server/ODBC package for Python

[Simon Brunning]

On 4/15/05, Graham <[EMAIL PROTECTED]> wrote:
> I need a SQL Server or ODBC package for Python.  Can anyone help please?
> Have search the Python packages under Database and there is no reference to
> either SQL Server or ODBC.

Top of the line is probably mxODBC
(). It's the Rolls
Royce of DB-API modules. It's not free for commercial use, but it's
worth every penny/cent/groat/whatever.

For commercial work where I've not been able to justify spending money
(in my bosses opinion at least), adodbapi
() works a treat.

-- 
Cheers,
Simon B,
[EMAIL PROTECTED],
http://www.brunningonline.net/simon/blog/
--
http://mail.python.org/mailman/listinfo/python-list

MS SQL Server/ODBC package for Python

[Graham]

I need a SQL Server or ODBC package for Python.  Can anyone help please?
Have search the Python packages under Database and there is no reference to
either SQL Server or ODBC.

Thanks
Graham


-- 
http://mail.python.org/mailman/listinfo/python-list