win32all odbc/MS SQLServer/bigint

[M.-A. Lemburg]

Bjorn Pettersen wrote:
>>From: M.-A. Lemburg [mailto:mal at lemburg.com] 
>>
>>Bjorn Pettersen wrote:
> 
> [...]
> 
> 
>>The win32all odbc module fetches BIGINTs as floats. That's
>>where you probably get your corruption from...
> 
> 
> That's what I thought from reading the code, but I didn't want to imply
> it publically in case I was wrong. I'm sure there are areas where loss
> of precision is acceptable, but in business applications there is a
> rather strong reaction when this happens, especially without
> documentation, or warning, and the fact that the Python type in the
> result set is a long not a float...

The odbc module returns a long in case floor(x) == x, but the
interfacing at C level is done using a C double.

>>>I looked at the mxODBC documentation, and it says it's doing 
>>>a conversion [automatic] to string for bigint data... (again 
>>>too slow). I'm pretty flummoxed, the ODBC C calls for 
>>>bigint/__int64 are pretty straight forward(?)
>>
>>They are ? __int64 is not exactly portable across platforms...
> 
> AIX, IRIX, SGI, Linux, SUN, HP, z/OS, OS/390, and Win* all support a 64
> bit integer type (I did the research at my last company, and only for
> the platforms we supported... -- I'm sure there are exceptions, but ODBC
> isn't a lowest common denominator specification by design).

Sure, but __int64 is only available in VC C++ AFAIK. Many
compilers have a "long long" type which could be used, but
then again, how do you know whether the ODBC driver was
compiled with the same C type and layout as the application
using it ?

It's one of those MS things again... the half cooked, "works
for me" kind of attitude.

>>BTW, conversion from strings to Python longs is done in C,
>>so it's not as slow as you may think.
> 
> I haven't looked at the Py_LongFromLONGLONG(sp?) algorithm, so you may
> be right. It was too slow when I wrote our odbc wrapper library in
> C++...

It's called PyLong_FromLongLong(), but only avialable if the
C compiler knows about long longs and only in Python 2.0 and
above.

It is faster than the PyLong_FromString() API which mxODBC
currently uses, but only if the ODBC driver does actually
manage BIGINTs as long longs internally (sending these
as strings over the wire is not uncommon, so you'd win on
the Python side, but lose on the ODBC driver side).

>>>As an added bonus, someone had the pleasure of showing me 
>>>that a simple select/fetchall (i.e. all C code for Python), 
>>>was over three times faster in C#..., and the win32all odbc 
>>>module has crashed python somewhere deep down in sql three 
>>>times in the last two hours (we're expecting this app to 
>>>run for ~4 days...). Looks like it's back to C++ (*sigh*).
>>
>>Have you tried mxODBC on this ? We have many customers running
>>mxODBC under Zope on heavily loaded sites for weeks... so far
>>without any complaints about its stability.
> 
> I will definitely try it later today (your reputation precedes you, but
> upper management is cheap, especially when something doesn't come from
> MS <groan>). My other options are wrapping our db lib as a Python
> extension (mostly done, but not DB API compliant), or recoding in C++
> (should be fast, I've got a working prototype :-)
> 
> Btw., are any of your customers running with 3Tb of collections
> ("someone didn't pay their bill") data, with throughput requirements and
> hard deadlines ("if you're not done by noon you must pay us back
> $50K"/hr) <wink>? (sorry if I come across too harsh, not my intention,
> but after going through the joy of formally proving my program could do
> no harm to either the data or the throughput of the server [oh, and
> being called at 3am on Sunday], my sense of humor seems to have gone
> into hiding -- maybe next week I'll have a beer and laugh about it :-)

I've heard similar things before :-)

Seriously, performance on the Python processing side is usually
not an issue (mxODBC is fast); it's network latency, database and
query optimizations that do matter.

> -- bjorn
> 
> ps: In case you were wondering, we're not doing this on live production
> data because we don't know what we're doing...

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Apr 13 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2003, Charleroi, Belgium:                        72 days left