FreeTDS Development Group

[craigs <s.craig AT andronics.com>]

Thanks for all the help guys.  Here's a more detailed description of my 
problem.

I get a current latitude, longitude value into my program, what i have 
to do then is compare it with my table( called PostCodes) which has the 
2.4 million records to find the record im looking for.  What i mean by 
this is bit hard to explain but here goes.

Table PostCodes columns include:
PlaceName, Town, Country, Latitude, Longitude, SweepRadius.

When i get my lat lon values i have to do a number of things, i call a 
function called LatLonDistance which takes my current lat lon values and 
works out the distance between the lat lon values in each Postcodes 
record(means i have to go into each record and work out the distance), 
if the distance is less than the SweepRadius this is the record im 
looking for, once found i do an insert into another table and call 
'return NO_MORE_RESULTS' in order to stop looping around the PostCodes 
table.

I cant see any other way to do this query because i need to call 
LatLonDistance on each record until i find the one i want.

Brian Bruns wrote:

> Of course, it goes without saying that you'll eventually run into
> situtations where you can only get an approximate match of the data
> you need due to external factors (merging with external data,
> calculations that can't be done by the server), in which case you'll
> need to return the smallest set you can containing your data and do as
> the loop did in this case.
>
> Brian
>
>
> On Thu, 10 Mar 2005 13:39:25 -0500, Lowden, James K
> <LowdenJK AT bernstein.com> wrote:
>  
>
> > > 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.
> >
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
> >    
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
>