[freetds] Cursors in freetds
sorton9999 at usa.net
Tue Jul 6 11:06:56 EDT 2004
I have not been ignoring this reply on purpose. I have been on vacation and
wanted to get back to my (sparse) notes in order to make a more lucid? reply.
I mentioned that I have at first tried to use freeTDS in the applications
that I have been writing for a project I'm involved in at work. I would now
like to try to clarify/correct my previous statements.
I originally thought that reusing connections would create a faster
throughput on data. After reading the document pointed to by the link given
by James below, I am "somewhat" convinced;) I still am of the mind that
connecting to the database, running a query and collecting the data for use by
an application is slower than running a query and collecting data and re-using
the first connection. All things being equal, network communication times can
vary greatly depending on usage loads. So I don't see how DB connections can
be ignored. Maybe I'm wrong on the definition or am not seeing something that
may be obvious here. I do know that most, if not all, enterprise level DBs
out there promote cacheing at many different levels. Does this include FD or
socket level caching as well?
One thing I do remember now is the fact that the commercial driver we use
requires a "per-connection" level license. So saving connections is best in
Finally, I want to add that I didn't find out that the C++ API we use also
limits the connection object to a single result set until after we switched to
the commercial driver. This is definitely my "bad"!!
I hope this is a more educated response on my part. I replied to the
original post in the early morning so I think my logic was clouded for that
reason too. I would appreciate more elucidation on the first paragraph as I
don't want to go on being ill informed or jumping to wrong conclusions:)
Northrop Grumman Mission Systems
"James K. Lowden" <jklowden at schemamania.org> wrote:
> On Sun, 27 Jun 2004, Steven Orton <sorton9999 at usa.net> wrote:
> > This is great news!! I was one interested party that was great
> > disappointed
> > 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.
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
sorton9999 at usa.net
INEPTITUDE: If you can't learn to do
something well, learn to enjoy doing
"Put your hand on a hot stove for a minute,
and it seems like an hour.
Sit with a pretty girl for an hour, and it
seems like a minute. THAT'S relativity."
-- Albert Einstein
More information about the FreeTDS