Full-Text search problem

Question

Post reply

Full-Text search problem

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
April 13, 2016 at 8:26 pm

#318105

Hi Everyone,
We have a database in SQL SERVER 2008 R2 and we have enabled Full-Text Search on one of our tables. We have a sp that call below TSQL
declare @Value int = 10
select
count(*)
from
dbo.Table1 t1 join
dbo.Table2 t2 on t1.Code1 = t2.Code1 and t2.Code2 = @Value left join
dbo.Table3 t3 on t1.Code3 = t3.Code3 and
t3.Code4 = 1
where
t1.code5 = 1 and
contains(t1.*,'test')
This SP has started to time out occasionally .When I checked the execution plan I noticed the process start from Full text search on Table1(on this table we have 40,000,000 rows), instead of applying the other filter's first and then Full-Text Search. I converted the Full-Text Search to use 'like' statement and in test environment in runs much faster.
I am trying to keep my full-Text search feature. Is there any way to force optimizer to use the filters first instead of Full-Text Search.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

Did you try updating meta data in fulltext index? Look in the properties.

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 2

Thanks for your reply. How do I know if i need to do that update?and will that cause any problem for end user. Is there any online feature for full-text search, so end user will not be affected.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You need to have a look into your indexing and how it correlates with the query.

It seems like the indexes on the tables involved are so irrelevant to the parameters in the query that optimizer decides that selectivity of the query will be not good enough and it's better to try its luck with full-text search first.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

And you can safely remove left join

dbo.Table3 t3 on t1.Code3 = t3.Code3 and

t3.Code4 = 1

left-joined table is not gonna affect COUNT(*) in any way.

_____________
Code for TallyGenerator

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Sergiy (4/14/2016)
And you can safely remove left join dbo.Table3 t3 on t1.Code3 = t3.Code3 and
t3.Code4 = 1
left-joined table is not gonna affect COUNT(*) in any way.

Woah, careful there!

This statement is only true if the combination of columnst Code3 and Code4 in Table3 is guaranteed to never match more than a single row. In other words, there has to be a PRIMARY KEY constraint, a UNIQUE constraint, or an unfiltered UNIQUE index on the combination (Code3, Code4) or a subset thereof. [Or, in this specific case, a UNIQUE filtered index on Code3 only with filter predicate Code4 = 1].

If there can be duplicates, COUNT(*) will most definitely be affected!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Smendle Hall of Fame Points: 3969 More actions · Answer 6

My thought is t3.code4=1 is unique and therefore the count will never be more than the base table (t1) so I agree you can remove that left join for count purposes as it will never be more than the count of t1.