>> a site search (field in SQL Server database)
Are you using Verity to index the database, the Free-text search feature in
SQL to index a collection or just a standard SQL query?
In one of our file archives we use SQL's search. We created a collection of
all the fields (description, author, name...etc) in the SQL server. Then on
the website we parse the input from the search field stripping non-search
terms, punctuation and Noise words and create a simple search string from
it. So if someone entered "Red,blue.green silver" we would parse it and get
three search strings of:
SQLSearchString1 = red AND blue AND green AND silver
SQLSearchString2 = red NEAR blue AND green AND silver
SQLSearchString3 = red OR blue OR green OR silver
Then we perform three union queries weighting the Rank field for each query
by importance and then sort the union by rank to obtain relevance. Each
subsequent Query excludes the results found in the previous so we do not
wind up with duplicate records.
SELECT (KEY_TBL.RANK * 1000) AS RANK, ID, <relevant_columns>
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, *, '#SQLSearchString1#*' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]
UNION
SELECT (KEY_TBL.RANK * 100) AS RANK, ID, <relevant_columns>
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, FileKeywords, '#SQLSearchString2#' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]
WHERE ID NOT IN((SELECT ID
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, *, '#SQLSearchString1#*' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]))
UNION
SELECT (KEY_TBL.RANK) AS RANK, ID, <relevant_columns>
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, *, '#SQLSearchString3#' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]
WHERE ID NOT IN( (SELECT ID
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, *, '# SQLSearchString1#*' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]))
AND ID NOT IN((SELECT ID
FROM filesdata AS FilesTable INNER JOIN
CONTAINSTABLE(filesdata, FileKeywords, '#
SQLSearchString2#' )
AS KEY_TBL ON FilesTable.ID = KEY_TBL.[KEY]))
ORDER BY 1 DESC
We get very good results on our data-set this way and it is faster and less
CPU intensive than Verity in our application because it puts the processing
into the separate SQL server instead of on the CF box running verity.
Best Regards,
Dennis Powers
UXB Internet - A website design and Hosting Company
690 Wolcott Road
P.O. Box 6029
Wolcott, CT 06716
Tel: (203)879-2844
http://www.uxbinternet.com/
http://www.uxb.net/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299912
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
