FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Jay Van Vark [mailto:jay AT vanvark.net]
> Sent: August 5, 2002 10:45 AM
> 
> I need to be able to get the number of rows 
> before I fetch all
> the rows or I need to be able to backup to the first row if I 
> need to loop
> through all the fetches (boy that would be slow).

Jay, 

The standard reply is:  No, you don't.  :)

You'd like to issue a query, determine the rowcount, allocate a buffer, and
fetch the rows.  But TDS servers return rows as they are found, before
they're all found, so the server itself doesn't know the rowcount until
they've all been fetched.  

You could take Frediano's suggestion; it will work.  It will also be slower
than allocating memory as the rows arrive.  Why?  The server is the slowest
thing on no legs in your system, and you'll ask it to do more.  You will:

1.      Wait for the server to completely finish you query.
2.      Add the overhead of creating a table and storing the rows.
3.      Impose two extra round trips to the database (in addition to the
real query, a fetch of @@rowcount and a "select * from #tmp").  

Note also that you're serializing the processing.  If you allocate memory
for rows as they arrive, the server can continue to process your query while
you're fetching what it found so far.   That's a big win if the query is
complex or there's some chance that not all the rows will be needed.  Memory
work on the client beats disk work on the server every time.  

In the old days, with just a standard C library, allocating memory on the
fly was a nuisance, so Sybase offered server-side cursors.  Nowadays, with
glib or C++, that's not as much of an issue.  

I hope you don't think I'm lecturing you.  You mentioned speed, so I thought
you might want to reconsider your design decisions/assumptions.  If you want
to optimize your system, naturally you have to exploit its strengths.  

Regards, 

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that for certain accounts we do not accept
orders and/or instructions by e-mail, and for those accounts we will not be
responsible for carrying out such orders and/or instructions. Kindly refrain
from sending orders or instructions by e-mail unless you have confirmed that
we accept such communications for your account. Please also note that to
satisfy regulatory requirements we review the outgoing and incoming e-mail
correspondence of staff members serving certain functions.