How do I avoid timeouts with SqlServer full text search?

Question

We're using SqlServer 2008. In SSMS, queries on the full text catalog might take 2-5 seconds the first time, but after that, return quite quickly.

On the other hand, running a query from via Linq2Sql will timeout.

Here's what we have:

The SQL Inline Table UDF

CREATE FUNCTION dbo.SearchArchiveFTS
(   
    @query nvarchar(255)
)
RETURNS @ret TABLE 
(
            ID      NVarChar(12) NOT NULL,
            snapshotDate    DateTime NOT NULL,
            -- about 10 more
)
AS BEGIN
    declare @innerQuery nvarchar(255)
    set @innerQuery = @query
    insert into @ret
    select      ID,
                snapshotDate,
                -- about 10 more
        from dbo.Archive a
        where contains(a.*, @innerQuery)
    return

Query in SSMS

select * from dbo.SearchArchiveFTS('query')
//3 seconds / 3k rows

Query in Linq2Sql

db.SearchArchiveFTS("query").ToArray();
// timeout exception

Any ideas on what the problem might be?

Answer 1

Check that your connection is not coming in with arithabort off. In SSMS it is ON

you can easily check like this

select arithabort,* 
from sys.dm_exec_sessions
where is_user_process =1

just find the SPID that is hitting the DB

You also try to see what happens when you do this in SSMS

SET ARITHABORT OFF 
select * from dbo.SearchArchiveFTS('query')

Does it now take a lot longer?

It is also possible that you are getting a bad plan from LINQ

You can clean out the procedure cache and memory buffers by running the following command

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Just be aware that it will wipe out all the plans on the server and SQL Server will have to recreate all of those and also read in all the data from disk again.......

Answer 2

I agree with @SQLMenace, when somehthing runs fast in SSMS but not from the application, it is usually a connection difference.

However, why use a function for something like that?

if you must use a function, why not use a table value function like this:

CREATE FUNCTION dbo.SearchArchiveFTS
(   
    @query nvarchar(255)
)
RETURNS TABLE 
AS RETURN
(
    select      ID,
                snapshotDate,
                -- about 10 more
        from dbo.Archive a
        where contains(a.*, @query)
);

Answer 3

The issue appears to be related to a feature of SQL Server, where the FTS indices are unloaded after a period of inactivity. A background job to keep them fresh solved the problem.