April 13, 2012 at 3:18 pm
As I've recently tackled some Full-text performance problems, I'll throw out some possibilities.
1. The FTS engine does have some caching in it. There is no documentation on what/when/how it caches, but it does seem snappier on the second+ executions. I'm guessing that when it parses the contains query text, it does some form of caching. it may go as far as caching the number of matching rows with could change the query plan on future executions.
2. In some cases, Sql server will use a loop join with a fulltext seek on the inside of the loop when it thinks that the relational predicates are very selective compared to the fulltext query. Unfortunately, the optimizer seems to massively underestimate the cost of a single seek into the Full Text index. This has the effect of taking queries from sub-second to minute in duration. The solution (as mentioned in a previous post) is to insert into a temporary using CONTAINSTABLE , then joining using the temp table.
3. Beyond this, you'd be looking at performance issues due to extremely high volumes of inserts/updates/deletes into the table, or concurrency issues due to high numbers of simultaneous queries (100's at a time). This seems unlikely in your situation.
My guess is number 2, and the "FORMSOF(INFLECTIONAL ... " is very expensive to probe with your dataset. Joining against CONTAINSTABLE is not enough to prevent a loop join with an inner FTS probe, you have to use a temp table.
Can you try using a temp table and see if you can reproduce the issue?
April 13, 2012 at 3:34 pm
SpringTownDBA (4/13/2012)
As I've recently tackled some Full-text performance problems, I'll throw out some possibilities.1. The FTS engine does have some caching in it. There is no documentation on what/when/how it caches, but it does seem snappier on the second+ executions. I'm guessing that when it parses the contains query text, it does some form of caching. it may go as far as caching the number of matching rows with could change the query plan on future executions.
2. In some cases, Sql server will use a loop join with a fulltext seek on the inside of the loop when it thinks that the relational predicates are very selective compared to the fulltext query. Unfortunately, the optimizer seems to massively underestimate the cost of a single seek into the Full Text index. This has the effect of taking queries from sub-second to minute in duration. The solution (as mentioned in a previous post) is to insert into a temporary using CONTAINSTABLE , then joining using the temp table.
3. Beyond this, you'd be looking at performance issues due to extremely high volumes of inserts/updates/deletes into the table, or concurrency issues due to high numbers of simultaneous queries (100's at a time). This seems unlikely in your situation.
My guess is number 2, and the "FORMSOF(INFLECTIONAL ... " is very expensive to probe with your dataset. Joining against CONTAINSTABLE is not enough to prevent a loop join with an inner FTS probe, you have to use a temp table.
Can you try using a temp table and see if you can reproduce the issue?
I know very little about FTS, so how does it handle BLOB data types since the OP has an NVARCHAR(max) column in the FTI?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2012 at 4:13 pm
Jack Corbett (4/13/2012)
I know very little about FTS, so how does it handle BLOB data types since the OP has an NVARCHAR(max) column in the FTI?
The base table is only read during population, not during querying, so no difference.
If you look at the query plan for "select * from containstable(.....)" you'll see that it doesn't touch the base table at all.
during population, the effect of the column storage is the same as for a SELECT statement.
April 13, 2012 at 7:55 pm
SpringTownDBA (4/13/2012)
Jack Corbett (4/13/2012)
I know very little about FTS, so how does it handle BLOB data types since the OP has an NVARCHAR(max) column in the FTI?
The base table is only read during population, not during querying, so no difference.
If you look at the query plan for "select * from containstable(.....)" you'll see that it doesn't touch the base table at all.
during population, the effect of the column storage is the same as for a SELECT statement.
Great. Thanks for the clarification. This was what I thought might be causing the problem, but you seem to have eliminated that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2012 at 7:26 pm
Hey Steph, we can't help you if you don't reply. You have access to the greatest sql minds in the world, FOR FREE.
You might want to try to take advantage of this gift and grace us with 1 reply per day.
April 17, 2012 at 4:56 am
Hi, thanks, I really appreciate your help.
I have problems with my laptop, I was not able to work on it for the past 2 days... I've to go to work now but I'll come tonight to read and reply on your messages.
Thanks again!
April 18, 2012 at 3:49 pm
Can you try using a temp table and see if you can reproduce the issue?
Hi!
By temp table, do you mean to create a similar table, with the same content, and the same index? I'm not very familiar with SQL architecture, so I want to be sure to de the right thing.
Thanks!
April 19, 2012 at 8:25 am
CameleWeb (4/18/2012)
Can you try using a temp table and see if you can reproduce the issue?
Hi!
By temp table, do you mean to create a similar table, with the same content, and the same index? I'm not very familiar with SQL architecture, so I want to be sure to de the right thing.
Thanks!
here's a sample that demonstrates how I recently incorporated FTS into a "catch-all" search stored proc with very good performance:
CREATE PROC ap_SearchItems
(
@IncludeContainsCondition VARCHAR(500)
, @StartingDateFrom DATETIME
, @StartingDateTo DATETIME
, @EndingDateFrom DATETIME
, @EndingDateTo DATETIME
)
AS
SET NOCOUNT ON
CREATE TABLE #fullTextMatches
(
ItemId INT NOT NULL
PRIMARY KEY
)
INSERT INTO #fullTextMatches
(ItemId
)
SELECT
i.
FROM
CONTAINSTABLE(items, SearchTerms, @IncludeContainsCondition) i
SELECT
Items.ItemTitle
, Items.ItemDescription
, Items.StartDate
, Items.CategoryId
FROM
Items
WHERE
(@IncludeContainsCondition IS NULL
OR (ItemId IN (SELECT
ItemId
FROM
#fullTextMatches))
)
AND (@startingDateFrom <= StartingDate
OR @StartingDateFrom IS NULL
)
AND (@startingDateTo >= StartingDate
OR @StartingDateTo IS NULL
)
AND (@EndingDateFrom <= EndingDate
OR @EndingDateFrom IS NULL
)
AND (@EndingDateTo >= EndingDate
OR @EndingDateTo IS NULL
)
OPTION
(RECOMPILE)
/* FullText search generates recompiles anyway based on the contains condition,
so using dynamic sql for plan reuse isn't much of a benefit. This sample will
eliminate the join between #fullTextMatches and Items if @IncludeContainsCondition
is null. (at least on SQL 2008 SP3)
*/
April 19, 2012 at 6:20 pm
April 21, 2012 at 10:15 am
#1 - create temp table as coded already
#2 - Insert the results from the fts
#3 join back to the base table using the temp table to filter the correct results.
I know it looks like a detour but it's actually faster that way in that particular case (because of the blob).
April 21, 2012 at 11:52 am
You just lost me... ::crazy:
I know the basis of SQL, what you ask me to do is out of my knowledge. As I said, I found this SQL Request on the Web when I was looking for something for a good search engine for my websites. I don’t even know how to do what you ask me to do. Sorry!
April 21, 2012 at 6:34 pm
I'd suggest hiring someone more experienced with MS SQL Server to help you on your project.
April 21, 2012 at 7:24 pm
April 23, 2012 at 9:41 am
Hi Camele, Would you mind telling how did you get this Query plan ? I meant the method of getting this plan as i am fairly new to this one and wanted to know how to get the plan the way you posted here
April 23, 2012 at 5:09 pm
Hi!
Open your Microsoft SQL Server Management Studio, click New Query, and type your SQL request. In the Query menu, select the Include Actuel Execution Plan option, and then, Execute your query. You will see the Results tab, the Messages tab, and the Execution Plan tab at the bottom. Click on it, and then right click in the window and select Show Execution Plan XML.
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply