views:

78

answers:

1

I have a query like this (created by LINQ):

SELECT [t0].[Id], [t0].[CreationDate], [t0].[CreatorId]
FROM [dbo].[DataFTS]('test', 100) AS [t0]
WHERE [t0].[CreatorId] = 1
ORDER BY [t0].[RANK]

DataFTS is a full-text search table valued function. The query execution plan looks like this:

SELECT (0%) - Sort (23%) - Nested Loops (Inner Join) (1%) - Sort (Top N Sort) (25%) - Stream Aggregate (0%) - Stream Aggregate (0%) - Compute Scalar (0%) - Table Valued Function (FullTextMatch) (13%)
                                                          |
                                                          |
                                                          - Clustered Index Seek (38%)

Does this mean that the WHERE clause ([CreatorId] = 1) is executed prior to the TVF ( full text search) or after the full text search? Is the TVF looking at the narrowed data set (narrowed by the WHERE clause), or at the entire table?

The TVF looks like this:

FUNCTION [dbo].[DataFTS] (@searchtext nvarchar(4000), @limitcount int)
RETURNS TABLE
AS
RETURN  
SELECT * FROM Databook
INNER JOIN CONTAINSTABLE(Databook, *, @searchtext, @limitcount) 
AS KEY_TBL ON Databook.Id = KEY_TBL.[KEY]

Thank you.

A: 

The WHERE is applied as part of "Nested Loops (Inner Join)" I suspect

Is the tvf inline or multi-statement? If inline I'd expect it earlier, but it looks multi-statement (it's a blackbox) hence the stream and sort operators with the filter later.

Edit:

With the definition, I see that the WHERE is being applied after the TOP (@limitcount). To be precise, the WHERE is applied to the table in a different place (clustered index seek, likely) and the tvf results filtered by the JOIN (see above)

gbn
I've added the TVF source. Not sure what the difference between inline and multistatement is. I'm trying to get the TVF to look at the narrower data set, instead of the entire table, if possible.
Alex