[freetds] Cursors in freetds
James K. Lowden
jklowden at schemamania.org
Mon Jun 28 02:23:49 EDT 2004
On Sun, 27 Jun 2004, Steven Orton <sorton9999 at usa.net> wrote:
> This is great news!! I was one interested party that was great
> to find out that FreeTDS did not have cursors available. It hurt so
> much that I had to scrap plans to use this driver and went to a
> commercial vendor. I would love to see this functionality be
> incorporated into the ODBC module as well. I am not up on the technical
> issues, but as a user, it would be good to have multiple resultsets
> handled with a single connection. The API I'm using does not handle
> this and I'm not even sure that the commercial driver does either. The
> main reason I'm interested in this is simply one of speed. We deal in
> multiple connections with multiple machines all hitting one backend DB.
> It seems pointless to connect and dump the connection with every query
> we need to run. Connect times are typically the most expensive and it
> would boost our throughput immensely to re-use the connection.
I'm a little puzzled by your post, for couple of reasons.
Just to be sure we're talking about the same thing:
If the API you're using from the commercial vendor doesn't support
"multiple resultsets handled with a single connection", and you opted to
use it because FreeTDS lacks that feature, what advantage does it bring
On the issue of speed. In most applications, cursors won't buy speed.
They sometimes offer the programmer convenience he wouldn't have
otherwise, and of course a degree of interaction that serial resultset
handling doesn't provide (which can be useful in support of user
interfaces). But for sheer speed, most of the time it's best to write the
queries such that they return precisely what's needed, and read the whole
resultset in turn.
It's not necessary to form and drop connections ad hoc; you can maintain a
few connections per user. With today's hardware, that's rarely a problem.
Not that I don't think cursors are fine and dandy in their place. It's
just that you raised the issues of "competing" functionality and speed,
which don't strike me as their primary advantages.
More information about the FreeTDS