I'm not sure if full text search is the best tool for what you are doing.
The type of fuzzy search that SQL FTS uses while doing FreeText searches
will do linguistic stemming and expand the search to singular and plural
forms of the search phrase. So it seems to me you are doing a freetext
search to match
Mr. John Q. Public with J.Q.Public, J. Q. Public, John Public, etc. It
simply won't work for address and name.
It also seems that you are executing the query once for each entry you have
in your table. After you return matches, do you prune/consolidate these
matches from the table?
I also don't understand why you are doing a Contains query on the zip code?
Surely this should be an equality match or perhaps a match with left(zip,5)
=left(@vOuterZip, 5), or perhaps a case statement so you could avoid the
left(zip,5) and merely search on zip.
Lastly I strongly urge you to have a look at commercial products which do
exactly what you are looking for and have logic built into them to handle
all the possible cases of street name combination, or name variants, as well
as compensating for spelling errors, like the product in the below link.
http://www.name-searching.com/Correct_Address.html --
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html [quoted text, click to view] "Ahsan" <Ahsan@discussions.microsoft.com> wrote in message
news:3DB4A82E-023B-43D5-BE38-042B41846B71@microsoft.com...
> Operating System: Windows 2000 Server
> Server Brand: DELL (Intel Penium 111, 1133 MHz)
> Sql Server Standadrd Edition
> Ram: 2GB
> Processor: 1
> Fulltext Index Size: 52MB
> Number Of Rows In Table: 1.2 Million Aprox
> SQL SERVER MEMORY SETTINGS: FIXED, 1945 MB
> MINIMUM QUERY MEMORY SETTING: 1048576 KB
> Average Records Processing Per Minute : 7.2
> Average Records Processing Per Hour : 72
> Target NUmber Of Rows: 1.1 Million
>
> stored procedure i am using is used to remove duplications from the
contacts
> list, I altered the few parts of stored procedure and checked every
statement
> using profiler,the only one taking long time to execute is the FULL TEXT
> SEARCHES one,
>
>
> SELECT Contact_ID From Contact22 con
> WHERE
> con.Gender = @vOuterGender
> AND (Con.Reported = 0 or con.reported is null)
> AND Contains(con.zip,@vOuterZip)
> AND Freetext(Address, @vOuterAddress)
> AND Freetext(Con.Full_Name,@vOuterName)
>
>
> as i mentioned that I have 1.1 million rows table on which fulltext is
> enabled on three cloumns
>
> Address,zip and Fullname (total size after full population is 52MB)
>
> so what all my stored procedure does is take every record from the contact
> table and matches it against others presnet in the table(1.1 million rows)
> using fulltext searches, now these full text searches are taking time!!!
>
>
> Currrenlty with all the settings I mentioned above my virtual memory is
set
> to 1.5 times then the actual RAM, my database and full text indexes are on
> the same drive(thats the only choice) and it is execting at the speed of
>
> 20000 rows in 24 hours which means it required almost 55 days to finish,
> unacceptable.....
> can any one tell me that how to improve the performance in a senario like
> mine for full text seaches? or is it the standard speed every one gets
from
> full text searches ?
>
