issues with full text search (especially views and pick lists)

Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
-FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
-Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
-Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
-Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!
baraka,
Unfortunately, SQL Server 2000 FTS does not directly support the FT Indexing
of views. However, you can include FTS queries *in* views, but not create FT
Indexes *on* views in SQL Server 2000.
Since you're testing Yukon (SQL Server 2005), have you downloaded and
installed the June CTP version? If not, I'd highly recommend that you do so
and then create one of your views as an "indexed view" as FT Indexing an
indexed view is supported in SQL Server 2005.
Relative to merging ranking for multiple tables (in SQL 2000 or SQL 2005),
try the following example to dump them into a
temp table, the below option as a few advantages, especially as it relates
to "summing" via the MAX function the RANK value as well as using a group
by:
CREATE TABLE #FTSQueryResult (PK_ID int, Rank int)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 1st query)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 2nd query)
Select PK_ID, Max(Rank) as Rank From #FTSQueryResults
Group by PK_ID
In regards to performance and scalability, for SQL 2000 its less an issue
with amount of text, and more a factor of the number of rows and the
language of the text in the FT-enable columns as well as your server's
configuration - number & speed of procs, amount of RAM and speed of your
disk drives. Review SQL 2000 BOL title "Full-text Search Recommendations"
for more info! As for SQL 2005, I've worked with a customer (now in Yukon
TAP) who FT-indexed a table with 180 Million rows in 8 hours, while using
SQL 2000 on the same server and same database and FT-enable table took over
6 weeks to complete! Checkout the case study of an internal MS application
"SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!433.entry
I'd highly recommend that you review all of the links at "SQL Server 2000
Full-Text Search Resources and Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"baraka" <barak.benezer@.gmail.com> wrote in message
news:1122341762.314188.223230@.g14g2000cwa.googlegr oups.com...
> Hi, I am trying to implement a global full text search on our SQL
> Server. Our app has several entities that are stored in the DB. I would
> like to be able to search for 'John Doe' and get results in all types
> of entities. Problem is:
> - FT Search does not crawl views. Unfortunately, each entity type in
> our system is not stored in full in one table. This is because we are
> using pick lists and look ups. For instance: Industry type is stored as
> a code that represents an entry in an industries table. However, all
> these values are joined in a view to create all the required fields for
> the entity a full text search query and index are created per table.
> What would be an effective way to work around this problem? (I don't
> see any solution to this problem in Yukon either.)
> - Ranking: both 2000 and Yukon do not allow for merging of rankings
> from several tables/queries. However, it is important to us to be able
> to display results from several tables and sorted in a logical way. Any
> suggestions/work arounds?
> - Performance and scalability: How many rows and/or how many GB can I
> have in my DB and still get good FT search query performance (less than
> 5 seconds)? (I need data for both 2000 and Yukon)
> - Same with regards to indexing: ideally we would like to keep the
> index up to date in real time. We would like to use the track changes
> feature. However, our app allows many users to be logged in and
> edit/delete/add entries in the DB. What is the maximum amount of DB
> transactions per minute (second?) that will still allow us to keep the
> index updated in real time? (I need data for both 2000 and Yukon)
> -
> I would greatly appreciate any suggestions/tips/info/workaround.
> Thanks!
>

Issue On Views

I have a view which has a date field. However, this date field is in the form of a number: 20041401. There are also places where the date is 0 (zero). I want to get this date to look like 1/14/04. I did this by creating a view on the first view, and used case to set the 0 (zero) values to null, and then I created another view on the last one to convert the not null values to a date using cast. Is there a simpler way to do what I want? Pretty soon I will lose track of all my views.
Thanks.Maybe you should cast or convert the datetime column the right way in your first view.|||I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.|||Not sure I follow..but...

DECLARE @.x int
SELECT @.x = 20041401
SELECT CONVERT(varchar(10),
CONVERT(datetime,SUBSTRING(CONVERT(char(8),@.x),7,2 )
+ '/' + SUBSTRING(CONVERT(char(8),@.x),5,2)
+ '/' + SUBSTRING(CONVERT(char(8),@.x),1,4)),1)|||Originally posted by exdter
I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.

Really?

SELECT CONVERT(varchar(10),0)|||That's what I need. Thanks.|||I strongly agree with the notion that, if you're dealing with date-type information that hasn't been stored as a Date field (and which, e.g. for application compatibility reasons, can't be converted now), it is highly desirable that you should use a base view that Casts the value to a date; then base other views on that.|||And doen't that make it an non-updateable view?|||Thanks.