FreeTDS Development Group

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

> From: craigs
> Sent: Thursday, March 10, 2005 7:03 AM
> 
> i want to exit query when i get what i want, 
> theres over 2.4 millions records in my table so really need to get 
> this right, can someone advise me if the following code would work.

Bill is helping you get your dbcancel() code correct, and maybe some
other aspects of your application in the bargain.  I want to emphasize a
point he made, because it may help eventually (or sooner).  You didn't
ask for design advice, but even it's no use to you, it might help
someone else.  

Your table has 2.4 million rows.  Based on some criterion, you want to
pick out one and discard the others.  That's the genesis of wanting to
understand dbcancel() etc.  

You're not the first person to have this challenge, nor the first to try
to approach it this way.  It won't work very well, though.  You'll
require the server to send a great many rows over the wire that you
don't need, wasting server, network, and client resources.  In the worst
case, you'll need row number 2,399,999.  If every row is 100 bytes, the
server would find and send 240 MB for the 100 you want.  On average, the
row you want will be midway through the result set, wasting 120 MB per
query.  It will be roughly 1,000,000 times slower than necessary.  

The right way to do it is to tell the server what you want: write a
specific query for the row you're looking for.  The server will search
the data locally (much faster) send you the one fine row.  You'll call
dbnextrow() exactly twice: once to get the row and once to confirm there
are NO_MORE_ROWS.  As Bill suggested, a nice refinement is to package
the SQL in a stored procedure and call that with your parameter(s).
Send the query as "execute my_proc @my_param" or, better still, use the
dbrpc functions.  

If you formulate your query to return only what you want, the networking
and cancellation issues go away, and you can focus on your application.


HTH.  

--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 we do not accept account orders and/or instructions 
by e-mail, and therefore will not be responsible for carrying out such orders 
and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to 
inform and update our clients, prospects and consultants of developments 
relating to our services and products, would not like to receive further 
e-mail correspondence from the sender, please "reply" to the sender 
indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, 
NY 10105.