FTS performance issues

CG,

I assume that you have stripped down your query some for presentation here,
so I am a little confused. Is this the code that is being executed? Are
you getting this from SQL Trace (or SQL Profiler)? If so, I don't see
anything like that what I trace my server doing fulltext searches. (You are
using Microsoft SQL Server, I trust.)

So, please excuse me if I missed the point. The following comments may help
you clarify your question.

Microsoft's TSQL language does not use a COLUMN keyword before the list of
columns, in case that is what you meant. Or, you may have a column named
COLUMN.

COLUMN is a keyword, so the complaint about FULLTEXTALL is actually caused
by the COLUMN keyword. I get different error messages depending on server
version:
2000: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'COLUMN'.
2005: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FULLTEXTALL'.

If you square bracket COLUMN you can get the following to be accepted by the
parser. This would mean that you have a column named COLUMN and you are
aliasing it to FULLTEXTALL.
SELECT [COLUMN] FULLTEXTALL FROM

However, the following where clause will not parse, since you cannot refer
to the column alias in the where clause:
Invalid: WHERE COLUMN FULLTEXTALL = @p1
Valid: WHERE [COLUMN] = @p1
Valid: WHERE FULLTEXTALL = @p1

Finally, you mention that you are doing a fulltext search, but the command
is not showing any full text operators. Perhaps the above is Dynamic-SQL
and you are building the fulltext command and storing it in @p1, then
executing the whole string. If so, then the actual command is currently
invisible to me.

What I would expect this to look like is something more like the following,
using either CONTAINS, CONTAINSTABLE, FREETEXT, or FREETEXTTABLE:

SELECT FULLTEXTALL FROM
WHERE CONTAINS (FULLTEXTKEY, @p1)

Please feel free to clarify or post additional details.

RLF

"CG" wrote in message
news:6FF2D8D6-DC90-425F-94CB-FA2723460D24@microsoft.com...
> Hi All,
>
> We are using SQL FTS feature big time to allow search in our repository to
> data. Most of the data is text data stored in Varbinary type of column in
> html format.
>
> While doing performance testing we find out that following statement is
> being run many time with huge performance impact.
>
> SELECT COLUMN FULLTEXTALL FROM WHERE
> COLUMN FULLTEXTKEY = @p1
>
> If i run above mentioned query in SQL server 2005, i get following error
> :-
>
> Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'FULLTEXTALL'.
>
> I think it is an internal query that is being run by FTS engine to get FTS
> column data for a particular key.
>
> Can anybody provide more details on above statement?
>
> Like why and when this query gets fired?
>
> Is there any way to optimize it?
>
> Thanks.
>
> CG
>
>
>

Hi Russ,

Thanks for your response. I think, i should have clarified it with details.
I made some assumptions.

Just to clear, i use Quest Software Spotlight on SQL Server Enterprise -
Xpert Edition to find performance issues with out queries.

I was verifying queries using above mentioned tool. The query i mentioned is
run as shown below :-

SELECT COLUMN FULLTEXTALL FROM WHERE
COLUMN FULLTEXTKEY = @p1

This query is causing performance issue. I know for sure that none of our
stored procedure or direct query have above mentioned query.

I am assuming that SQL Server FTS engine creates this query to get fields
that needs to be FTS indexed for a particular table.

I think it might be internal query that can not be run by us.

Thanks.

CG


"Russell Fields" wrote:

> CG,
>
> I assume that you have stripped down your query some for presentation here,
> so I am a little confused. Is this the code that is being executed? Are
> you getting this from SQL Trace (or SQL Profiler)? If so, I don't see
> anything like that what I trace my server doing fulltext searches. (You are
> using Microsoft SQL Server, I trust.)
>
> So, please excuse me if I missed the point. The following comments may help
> you clarify your question.
>
> Microsoft's TSQL language does not use a COLUMN keyword before the list of
> columns, in case that is what you meant. Or, you may have a column named
> COLUMN.
>
> COLUMN is a keyword, so the complaint about FULLTEXTALL is actually caused
> by the COLUMN keyword. I get different error messages depending on server
> version:
> 2000: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'COLUMN'.
> 2005: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'FULLTEXTALL'.
>
> If you square bracket COLUMN you can get the following to be accepted by the
> parser. This would mean that you have a column named COLUMN and you are
> aliasing it to FULLTEXTALL.
> SELECT [COLUMN] FULLTEXTALL FROM
>
> However, the following where clause will not parse, since you cannot refer
> to the column alias in the where clause:
> Invalid: WHERE COLUMN FULLTEXTALL = @p1
> Valid: WHERE [COLUMN] = @p1
> Valid: WHERE FULLTEXTALL = @p1
>
> Finally, you mention that you are doing a fulltext search, but the command
> is not showing any full text operators. Perhaps the above is Dynamic-SQL
> and you are building the fulltext command and storing it in @p1, then
> executing the whole string. If so, then the actual command is currently
> invisible to me.
>
> What I would expect this to look like is something more like the following,
> using either CONTAINS, CONTAINSTABLE, FREETEXT, or FREETEXTTABLE:
>
> SELECT FULLTEXTALL FROM
> WHERE CONTAINS (FULLTEXTKEY, @p1)
>
> Please feel free to clarify or post additional details.
>
> RLF
>
> "CG" wrote in message
> news:6FF2D8D6-DC90-425F-94CB-FA2723460D24@microsoft.com...
> > Hi All,
> >
> > We are using SQL FTS feature big time to allow search in our repository to
> > data. Most of the data is text data stored in Varbinary type of column in
> > html format.
> >
> > While doing performance testing we find out that following statement is
> > being run many time with huge performance impact.
> >
> > SELECT COLUMN FULLTEXTALL FROM WHERE
> > COLUMN FULLTEXTKEY = @p1
> >
> > If i run above mentioned query in SQL server 2005, i get following error
> > :-
> >
> > Msg 156, Level 15, State 1, Line 1
> > Incorrect syntax near the keyword 'FULLTEXTALL'.
> >
> > I think it is an internal query that is being run by FTS engine to get FTS
> > column data for a particular key.
> >
> > Can anybody provide more details on above statement?
> >
> > Like why and when this query gets fired?
> >
> > Is there any way to optimize it?
> >
> > Thanks.
> >
> > CG
> >
> >
> >
>
>

this is an internal query used by the full-text indexing engine. Its
used by the indexer to get the key and the row data you are full-text
indexing.

You can lessen the impact of this by placing a covering index on your
full-text key and the data you are indexing.

Hi Hilary,

Thanks for your response.

Could you please elaborate more on following statement of yours :-

>You can lessen the impact of this by placing a covering index on your
> full-text key and the data you are indexing.

We have set FTS key as primary key . It is key of clustured index as well.

Please suggest if there is anything else that can help further.

Thanks.

CG

"Hilary Cotter" wrote:

> this is an internal query used by the full-text indexing engine. Its
> used by the indexer to get the key and the row data you are full-text
> indexing.
>
> You can lessen the impact of this by placing a covering index on your
> full-text key and the data you are indexing.
>

CG,

Here is what Hilary was talking about.

Let's say that your table has 50 columns in it, including the FTSKey column
and the FullTextData column. That might require scanning the table to read
every row.

However, you could create a covering index that included only the FTSKey and
the FullTextData column. In that case, the indexing service could scan only
the covering index, thus avoiding the I/O for the other 48 columns that are
also stored on the leaf page. This would look like:

CREATE NONCLUSTERED INDEX IX_FTS_Helper
ON dbo.FTSTable (FTSKey, FullTextDataColumn);

This would not be any help if your text is stored in a non-indexable column,
such as TEXT, NTEXT, IMAGE or for large VARCHAR / NVARCHAR. An index cannot
be greater than 900 bytes.
http://msdn.microsoft.com/en-us/libr...3(SQL.90).aspx

However, starting with SQL Server 2005, VARCHAR(MAX), NVARCHAR(MAX) and
VARBINARY(MAX) can be added as included columns on the index, which might be
worth checking out. That would be something like:

CREATE NONCLUSTERED INDEX IX_FTS_Helper
ON dbo.FTSTable (FTSKey)
INCLUDE (FullTextDataColumn);

By the way, after your earlier response I suspected that you were seeing
internal indexing code in your trace and original post, so I am happy that
Hilary confirmed that. (I had not set up a test trace before he responded.)

All the best,
RLF


"CG" wrote in message
news:1A7EC792-F257-4B23-85DC-F5715E9A2BD5@microsoft.com...
> Hi Hilary,
>
> Thanks for your response.
>
> Could you please elaborate more on following statement of yours :-
>
>>You can lessen the impact of this by placing a covering index on your
>> full-text key and the data you are indexing.
>
> We have set FTS key as primary key . It is key of clustured index as well.
>
> Please suggest if there is anything else that can help further.
>
> Thanks.
>
> CG
>
> "Hilary Cotter" wrote:
>
>> this is an internal query used by the full-text indexing engine. Its
>> used by the indexer to get the key and the row data you are full-text
>> indexing.
>>
>> You can lessen the impact of this by placing a covering index on your
>> full-text key and the data you are indexing.
>>