[freetds] Cursors in freetds

Steven Orton 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
this regard.
  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:)

  Steve Orton
  Software Engineer
  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.
> Steve, 
> I'm a little puzzled by your post, for couple of reasons.  
> Just to be sure we're talking about the same thing:
> http://www.freetds.org/faq.html#pending
> 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
> you?  
> 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.  
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

Steven Orton
sorton9999 at usa.net
INEPTITUDE: If you can't learn to do
 something well, learn to enjoy doing
 it poorly.
"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 mailing list