11

In SQL Server (2008), I have a FullText index on two columns, call them Table1.FirstNames and Table2.LastNames. After profiling some queries, I came up with the following results:

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR CONTAINS(LastNames, 'Bob')

=> 31 197ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE (FirstNames LIKE '%Bob%') OR CONTAINS(LastNames, 'Bob')

=> 1941ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR LastNames LIKE '%Bob%'

=> 3201ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob')

=> 565ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE FirstNames LIKE '%Bob%'

=> 670ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(LastNames, 'Bob')

=> 17ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE LastNames LIKE '%Bob%'

=> 3ms

This behaviour persists even if I rebuild the FullText index.

FullText is usually much faster than a LIKE query over large sets of data in a specific language, but why do query speeds slow down by an order of magnitude when I OR together two FullText clasues?

Andrew Song
  • 3,120
  • 1
  • 27
  • 22

3 Answers3

5

Does changing to using ContainsTable help?

It did here Adding more OR searches with CONTAINS Brings Query to Crawl

And the same answerer (Joe Stefanelli) managed to bring about a similar improvement by changing FREETEXT predicates combined with OR to a FREETEXTTABLE here SQL Server full text query across multiple tables - why so slow?

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

Perhaps you should read this: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

I'd take a look at the execution plan for each of those. I'm guessing you will learn quite a bit from that.

Here is a decent link that will show you how to display the execution plan as well as some tips on interpreting it.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486