pyodbc + unixodbc + freetds slow

[dstromberg]

I'm using pyodbc 2.1.8 (buitl this piece myself - I didn't see an Ubuntu package for it), unixodbc 2.2.11-21 and freetds 0.82-6build1 to talk to an MS SQL Server sytsem on Ubuntu Lucid.

The performance isn't stellar.

I noticed that many of the packets, while looking via wireshark, were short - just over 500 bytes. There's a claim out there that increasing the packet length (I believe this is BLOCKSIZ in the freetds source, but I'm not certain) doesn't help, but I've seen that increasing packet lengths can help a lot with transfers on gigabit links, even though it may not make much difference on 10BaseT or even 100BaseT - increasing the length keeps the CPU from becoming the binding constraint.

Anyway, I have much more detail here:
http://code.google.com/p/pyodbc/issues/detail?id=120&q=freetds

It seems like making the freetds driver use a large block size should help, but if you have any other thoughts on how to make ODBC transfers with TDS faster, I'm all ears!

Thanks for the great software!

[dstromberg (strombrg)]

More specifically, testing with sqsh -A 2048 is about 14 percent faster than sqsh -A 512 on a particular table in MS SQL Server. sqsh doesn't link against freetds but it does link against libct.

[Frediano Ziglio (frediano-ziglio)]

Use a proper protocol version. For mssql you should use at least protocol 7.0 which defaults to 4096 as packet size. Also newer version optimize network packets sizes packing multiple TDS packets into network packets.

[Steve Langasek (vorlon)]

Per the upstream response (comment #2), I'm closing this as invalid since it's a library configuration issue rather than a bug that needs to be changed in the code.