Menu
▾
▴
[python-sybase] Re: FW: Is Python Sybase module slow compared to Perl DBI?
|
From: Dave C. <dj...@ob...> - 2002-09-24 08:32:10
|
chuck> Oops...accidentially hit the send button. For a single table:
chuck>
chuck> time (secs) type/settings
chuck> ----------- ---------------------------------------------------
chuck> 160 Perl DBI
chuck> 320 sybase-0.34
chuck> 340 sybase-0.35pre2, fetchone
chuck> 265 sybase-0.35pre2, fetchone, locking=0
chuck> 153 sybase-0.35pre2, fetchmany, arraysize=32
chuck> 151 sybase-0.35pre2, fetchmany, arraysize=32, locking=0
chuck> 150 sybase-0.35pre2, fetchmany, arraysize=64
chuck> 150 sybase-0.35pre2, fetchmany, arraysize=64, locking=0
chuck> 155 sybase-0.35pre2, fetchmany, arraysize=128
chuck> 153 sybase-0.35pre2, fetchmany, arraysize=128, locking=0
chuck>
chuck> The wierd thing also it that the locking doesn't seem to affect
chuck> the run once I started using the fetchmany. Here is the new
chuck> source code I used:
chuck>
chuck> db = Sybase.connect(server, user, password, database) #, locking
chuck> = 0)
chuck>
chuck> # output the actual data
chuck> c = db.cursor()
chuck> c.arraysize = 64
chuck> c.execute("SELECT %s FROM %s" % (select, table))
chuck>
chuck> num_recs = 0
chuck> if os.path.isfile("%s.gz" % dumpfile):
chuck> os.remove("%s.gz" % dumpfile)
chuck> file = os.popen("gzip -c > %s.gz" % dumpfile, "w")
chuck> while 1:
chuck> rows = c.fetchmany()
chuck> if not rows : break
chuck>
chuck> for row in rows:
chuck> line = []
chuck> for i in range(len(row)):
chuck> line.append("%-*s" % (lengths[i], row[i]))
chuck> print >> file, "".join(line)
chuck> num_recs += 1
chuck>
chuck> file.close()
chuck> c.close()
chuck>
chuck> What is the locking doing? It is internal Python thread
chuck> locking, or is it Sybase locking?
The locking allows you to share database connections between Python
threads. It is implemented in the Sybase.py code - by turning it off
you disable all of the associated function calls to the standard
Python threading module.
If you look closer at Sybase.py and read the sybasect documentation
you will see that the low level module also implements locking for
multiple threaded programs. This is disabled in Sybase.py by the call
_ctx.ct_con_alloc(0) (the 0 turns off locking). In sybasect I have
implemented (I think) all of the documented rules for multi-threaded
access to the Sybase CT library. Since the connection level locks in
Sybase.py are much more coarse I turn off the fine grained locking in
the low level module. The low level module can be used in a
standalone fashion - Sybase.py is just one example of how you can use
sybasect.so.
The reason for the locking in Sybase.py is that a database connection
is implemented (in Sybase) via a TCP socket which talks the TDS
(Tabular Data Stream (I think)) protocol. TDS only supports a single
result set at a time over the socket - this represents a "small"
problem when you might want to have multiple cursors in your
multi-threaded program using the same connection...
So, what the Sybase.py locking code does is place a lock on the
connection when you do a Cursor.execute() or Cursor.callproc() then
releases the lock once the results have either been fetched or
cancelled. This makes the single result set limitation mostly
invisible to your Python code.
If you are not using threads in your application you may as well pass
locking = 0 to the Sybase.connect() function.
chuck> Thanks again for the help. I'd say that Python and Perl are
chuck> effectively now running at the same speed!
That is absolutely awesome!!! I have made some more slight changes
which sped my test program up by another 10%. I a version of the
changes I made to the python-sybase mailing list (minus the further
10% speedup):
http://object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
If I do not get any reports of problems in the next week I am going to
make a new release. Mind you I still have not had any report of
success as yet (excluding yours of course)...
Thank you very much for helping out.
- Dave
--
http://www.object-craft.com.au
|
