|
Home > Archive > SQL Server Full-Text Search > December 2006 > FTI, Searching and other Filters
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
FTI, Searching and other Filters
|
|
| Kyle Jedrusiak 2006-12-13, 7:13 pm |
| We have this table...
CREATE TABLE [dbo].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
& #91;HumanResourceID]
[int] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
[Description] [nvarchar](256) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
NULL,
[ContentType] [nchar](4) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
[Content] [image] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
[Version] [timestamp] NOT NULL,
[EmployeeID] [int] NULL,
CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
(
[ResumeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Content contains the bits that make up either Word or RTF documents.
We have FTI defined on Content / ContentType / DocumentID. Generally FT
searches are working.
The table contains over 130k documents.
Through our application we are limiting their searches to the top 1500 rank
of any FT search. (So as not to over-burden our server.)
This works when the want to search the table for documents within the entire
company.
But what they would really like is the top 1500 rank for documents within
their office.
Is there a way to partition the the table by an OfficeID with some what to
pre-filter so the FT search is only looking at Documents from one or more
OfficeIDs?
TIA - Kyle!
| |
| Kyle Jedrusiak 2006-12-13, 7:13 pm |
| I think I found my answer...although a lot of work.
Remove the FTI from the table.
Add an OfficeID column to the table and populate it.
Partition the table by the OfficeID column.
Create an Indexed View for each OfficeID
(Open a new office, the add a new OfficeID and a new Indexed View for that
OfficeID.)
(Close an existing Office, migrate the documents to a different office, drop
the FTI for that View and drop that View)
Add a FTI to each of the Indexed Views.
Mod the application so it knows what how to FT search one or more Indexed
Views and combine the results from multiple views if needed.
"Kyle Jedrusiak" < kjedrusiak@princeton
information.com> wrote in message
news:OitVbpuHHHA.1468@TK2MSFTNGP04.phx.gbl...
> We have this table...
>
> CREATE TABLE [dbo].[Document](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> & #91;HumanResourceID]
[int] NOT NULL,
> [Name] [nvarchar](256) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
> [Description] [nvarchar](256) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
> NULL,
> [ContentType] [nchar](4) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
> [Content] [image] NOT NULL,
> [DateEntered] [datetime] NOT NULL,
> [DateModified] [datetime] NULL,
> [Version] [timestamp] NOT NULL,
> [EmployeeID] [int] NULL,
> CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
> (
> [ResumeID] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> Content contains the bits that make up either Word or RTF documents.
>
> We have FTI defined on Content / ContentType / DocumentID. Generally FT
> searches are working.
>
> The table contains over 130k documents.
>
> Through our application we are limiting their searches to the top 1500
> rank of any FT search. (So as not to over-burden our server.)
>
> This works when the want to search the table for documents within the
> entire company.
>
> But what they would really like is the top 1500 rank for documents within
> their office.
>
> Is there a way to partition the the table by an OfficeID with some what to
> pre-filter so the FT search is only looking at Documents from one or more
> OfficeIDs?
>
> TIA - Kyle!
>
|
|
|
|
|