FTI, Searching and other Filters

We have this table...
CREATE TABLE [dbo].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[HumanResourceID] [int] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_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!
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@.princetoninformation.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,
> [HumanResourceID] [int] NOT NULL,
> [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_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!
>
|||Hello Kyle,
The other option is to add the office ID to the content (If the content was
editable i.e. text/html)
Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
SERVER DBA"')
If the content is editabel this is by far the more manageable, and scalable.
We did the index view thing and it is just not a neat solution. The token
thing is much easier.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> 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@.princetoninformation.com> wrote in
> message news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
|||Um, maybe I'm missing something here, but since you are using the rank
I'm assuming you are using CONTAINSTABLE or FREETEXTTABLE and joining
back to Document. I'm also assuming you are using a "TOP 1500" and an
"ORDER BY Rank" in your query. Could you then just add "AND OfficeID =
'USEROFFICEID'" to your WHERE clause after adding the OfficeID field to
the table?
On Dec 14, 7:32 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:[vbcol=seagreen]
> Hello Kyle,
> The other option is to add the office ID to the content (If the content was
> editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
>
>
>
>
|||Looks like I'm stuck.
In order to create a Full Text Index on my view, the view has to have a
unique index.
SQL 2005 doesn't allow you to create a index on a view if the view contains
an text, ntext, image or xml columns.
And mine does because we're storint the resume in an image column and the
resume is what we're after.
We can't add any special tokens, the content is an image field.
Any other ideas?
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
> Hello Kyle,
> The other option is to add the office ID to the content (If the content
> was editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and
> scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Kyle,
a) you can create an index on a view if you use VARCHAR(MAX) or
VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql 2005).
b) AFAIK, those "tokens" can be other columns from the same view/table that
are also indexed in that FT catalog. You just specify CONTAINS(*,... Instead
of CONTAINS(MyClobColumn,...
it should work.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
> Looks like I'm stuck.
> In order to create a Full Text Index on my view, the view has to have a
> unique index.
> SQL 2005 doesn't allow you to create a index on a view if the view
> contains an text, ntext, image or xml columns.
> And mine does because we're storint the resume in an image column and the
> resume is what we're after.
> We can't add any special tokens, the content is an image field.
> Any other ideas?
>
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
>
|||I will have to try changing the type over to VARBINARY(MAX). That may work.
It mght also be easier to FTI a second column since that will require less
program changes.
Thanks
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added Office nvchar(8) to my table and populated int
select count(DocumentID) from Document where contains(*, 'Office01 and
cobol')
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added a Office nvchar(8) column and populated it.
No matter what I specify for a search condition, it doesn't return what I'm
after.
For a test I tried
select count(DocumentID) from Document where contains(*, 'Office02 and
cobol')
'Office02' is in the new column, 'cobol' is in the image column.
If I search for them seperately there is overlap so the data is correct.
Ideas?
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||To be clear the 'Office' column will only ever hold an office identifier
liket 'Office01' or 'Office13'.
The Content column has the interedting data.
We want to use containstable to give us the top n by rank DocumentIDs for a
particular office.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uM6mqsRKHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I added a Office nvchar(8) column and populated it.
> No matter what I specify for a search condition, it doesn't return what
> I'm after.
> For a test I tried
> select count(DocumentID) from Document where contains(*, 'Office02 and
> cobol')
> 'Office02' is in the new column, 'cobol' is in the image column.
> If I search for them seperately there is overlap so the data is correct.
> Ideas?
> "Lakusha" <Lakusha@.excite.com> wrote in message
> news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
>

FTI population: Locking issue shutting down Full-Text Index population

We are implementing FTI on a Linguistic SQL Server Database for performance improvement. The maximum size of database is 20 GB and there are 27 million records in the table on which we are implementing FTI. When We did a small POC with 1.2 million records, the FTI worked fine. But now with 27 million records it' giving the error messages attached in the event logs below.

It looks like the system resources are insufficient for completing the FTI population, but can I pinpoint what is causing problem from the overall system? Can we make some firm conclusions by looking at the attached event logs?

FYI: the free disc space on which the FTI catalogs are kept is 55GB. The SQL Server configuration is 4 processors, 4GB RAM.

Any help in this regard is greatly appreciated. ThanksHi There,

You could always split the process by /8 or /16 different tables and then move them to the new location systematically and not all at one time.

(ISQL/W : Select percentage 25% from blah ...?)

FTI on whole Database

I am new at FULLTEXT INDEX, I wonder if there is a possibility to index the whole DB at once and search it in the same way rather than specifying the Table and the columns?

NO, and I can't imagine why one would consider that as a possibility.

I wouldn't consider searching datetime columns, or money columns, or numeric columns for a text string. So even if it were possible, it would amount to a terrible waste of time/effort searching unnecessary columns. As well as significant resources just to create and maintain indexes on columns that may never need to be searched.

|||

Ok, Here is the scenario, We have this huge DB from a client -about 6k Tables!!!!- I am looking for the column that contains a specific string? I thought of using the FULLTEXT INDEX to get all the instances that contains that strings.
Is there a way to do that? Definitely as you mentioned it will be a terrible waste of time/effort but I don't see doing this otherwise, searching the DB table by table will be worse.

|||

No matter how you cut it, that will be a resource intensive task.

Here is a procedure that I created that would do the search. Hopefully, it will give you a good idea or two. Expect it to take quite some time with 6k tables.

--*******************************************
-- Problem: Locate data in any column, any table
-- Demonstrates:
-- WHILE looping
-- Using Dynamic SQL (sp_executesql)
-- #Temp Table vs. Table Variable

--*******************************************

SET NOCOUNT ON

DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50),
@.ColumnName varchar(50),
@.FieldValue varchar(250),
@.SQLCommand nvarchar(1000),
@.ValueToFind varchar(100)

-->
SET @.ValueToFind = 'mexico'
USE Northwind -- For demo only, not needed

-->

DECLARE @.MyTable table
( RowID int IDENTITY,
TableName varchar(50),
ColumnName varchar(50)
)

CREATE TABLE #FoundTable
( RowID int IDENTITY,
Tablename varchar(100)
)

INSERT INTO @.MyTable
SELECT
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )

SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1

WHILE ( @.Counter <= @.TotalRows )

BEGIN

SELECT
@.TableName = TableName,
@.ColumnName = ColumnName
FROM @.MyTable
WHERE RowID = @.Counter

SET @.SQLCommand = 'IF EXISTS ( '+
'SELECT 1 FROM [' + @.TableName + '] ' +

'WHERE [' + @.ColumnName + '] LIKE ''%' + @.ValueToFind + '%'' ' +
' )' +
'INSERT INTO #FoundTable ' +
' SELECT ''[' + @.TableName + ']([' + @.ColumnName + '])'''

EXECUTE sp_executesql @.SQLCommand

SET @.Counter = ( @.Counter + 1 )

END

SELECT * FROM #FoundTable

DROP TABLE #FoundTable

/*
RowID Tablename
-- --
1 Invoices(Salesperson)
2 Employees(LastName)
3 Employees(PhotoPath)
*/

|||Thanks!!!!!!!!!!! It worked, I had some issue with the variables, I was getting errors "Must declare the scalar variable "@.TotalRows". which I think it is the result of the variables scope.

|||Sorry, I had left a [GO] in the script, and it starts a new variable scope.

FTI and multiple columns - another question, but opposite behaviour?

I have a table with 2 FTI columns, but my query results seems strange. If I
run this query:
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
it returns no rows.
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
this returns one row as both words are in the same FTI column.
AND CONTAINS( J.*, '"FIFE"' )
returns the same row, as FIFE exists in the other FTI column.
So, why doesn't the top query return the row in the results? Is this normal
behaviour? Is there anything I can do to make it work?
Thanks.
A contains query can't look across columns, a freetext query can. So if your
hits come from different columns a freetext query will return a hit, whereas
a contains won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe Bloggs" <Joe.Bloggs@.acme.com> wrote in message
news:%23C6lxLZcFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have a table with 2 FTI columns, but my query results seems strange. If
I
> run this query:
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
> it returns no rows.
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
> this returns one row as both words are in the same FTI column.
> AND CONTAINS( J.*, '"FIFE"' )
> returns the same row, as FIFE exists in the other FTI column.
> So, why doesn't the top query return the row in the results? Is this
normal
> behaviour? Is there anything I can do to make it work?
> Thanks.
>