Re: Better search algorithm?

try a good book on "algorithms" such as baase. searching algorithms are one of the most popular topics in computer science. i am sure you can find plenty on this topic. the challenge will be narrowing
it down to your area.

"Jason Chancellor" <newsgroups_at_makethematch.com> wrote in message news:a19c9a2e.0107231155.7f3c4dd8_at_posting.google.com...
> Hello, I am working on a project which is beginning to show some
> performance issues, and I have been trying some different techniques
> with no better results....so I thought I would ask around for some
> advice.
>
> The project is a matchmaker which basically pairs up people based on a
> questionnaire and their distance from each other. IE, I would like to
> find all people who are girls and live 30 miles from me. The database
> is MS Sql Server 7
>
> The tables in question are as follows :
>
> USER_INFO
> -----------------
> user_id numeric(10,0)
> zipcode int
> sex int
> age int
> etc......
>
> ZIP_CODE
> ---------------
> zipcode int
> longitude float
> latitude float
>
> SEARCH
> ------
> search_user_id numeric(10,0)
> found_user_id numeric(10,0)
> distance float
>
> OK, heres what I was trying. I was selecting a group of users from
> the USER_INFO table based on their answers to questions and inserting
> them into the SEARCH table. The search_user_id is who requested the
> search, and the found_user_id is who the search produced (depending on
> how broad the search was, this insert could be 1000's of rows. After
> the insert, I had a stored proc which went through the SEARCH table
> and calculated the distance the two people were from each other based
> on the longitude and latitude of their two zipcodes, and then this
> distance was used to determine if these two users were close enough
> together. This distance calculation is what is killing my
> performance, 1st because its a complicated algorithm, and two
> because I have to basically go through 1000s of rows and do 1 update
> at a time.
>
> I would like to be able to calculate the distance at the same time I
> am selecting the users based on their questions. My initial thought
> was to create a table which stored the precalculated distances between
> all zipcodes in America and then join in this table when doing my
> search. However, after a few calculations I realized that this would
> require this distance table to have over 640 million rows (thats a
> couple too many)
>
> So here I am, totally out of ideas... Can anyone think of a better
> way to perform the search I am trying to do, or can anyone point me
> to a book or a URL of some info that might be of help?
>
> Thanks for your time.
> Jason Chancellor
> jason_chancellor_at__NOSPAMHERE_hotmail.com
Received on Tue Jul 24 2001 - 05:06:52 CEST