2

This is in continuation to my Previous Query

DDL

CREATE TABLE [dbo].[t](
    [words] [varchar](1000) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

DML

insert into t(words)values('this is my laptop')
insert into t(words)values('this does not contains heqadphone')

These two inserts are iterated 250 times...

SQL Queries - 1

SELECT * FROM 
t as t
JOIN CONTAINSTABLE(t, words,'"*heqadphone"') fulltextSearch
ON
t.[Id] = fulltextSearch.[KEY]

SQL Query - 2

Select * from t where words like '%heqadphone%'

Confusions

Normally we are advised to not use double %% as stated in my later query. But After checking the SQL Profiler Reads and Duration

Query 1 shows more reads/duration . Click the below link to see details

SQL Profiler Reads and Duration for Query - 1

Query 2 shows less reads/duration . Click the below link to see details

SQL Profiler Reads and Duration for Query - 2

Can you confirm, Ideally which query can be good to use?

Community
  • 1
  • 1
Pankaj
  • 9,749
  • 32
  • 139
  • 283

3 Answers3

5

You are comparing two different features with different functionality: CONTAINSTABLE gives you additional information such as ranking that LIKE does not. So the first question is, do you actually need fulltext features or not? If you do, then you have no choice: you must use CONTAINSTABLE.

On the other hand, if you don't need FTS features but just want the fastest way to search a column for a single word, then the best approach is exactly what you're doing: test both options with a realistic data set and see which one performs better in your environment. If LIKE performs better then you can use it, but as erikkallen mentioned make sure you test with the same volume of data that you'll have in production.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
1

How much data will you have. If you have 250 rows you can do pretty much anything without it being slow. If that is your amount of production data, then do whatever you want. If not, run your tests with the amount of data you expect to have in your production system.

erikkallen
  • 33,800
  • 13
  • 85
  • 120
0

I think for performance measurement/analysis you need far more data than just 250*2 = 500 rows. You should try to test for the data in the range of 100,000 records

NG.
  • 5,695
  • 2
  • 19
  • 30