February 9, 2016 at 6:23 am
I'm having performance problems with like statements. I did some searching on the internet, which say's I need to use the 'Contains' predicate. However, when I implement this, is doesn't work properly.
I have a table with several columns (about 25 columns) with a FullTextIndex on 2 columns (name and number). The number column can include alphanumerical signs.
When i do a search with a Like statement, it will find all data, including where parts of the name or number are what i search for, however with contains it doesnt.
In example:
Table: 'Employee', columns are 'ID (PK) int', 'Name ('Nvarchar(40)), Number (Nvarchar(25)), 'SomeOtherColumns'.
When I query:
select *
from Employee
where Name like '%Jo%'
It finds 'John doe', 'TheJo', 'Jo'
When i query:
select *
from Employee
where Contains(Name, '"Jo*"')
It finds 'John doe', 'Jo'. But I also want to display the 'TheJo' record.
Is there a way to archieve this?
February 9, 2016 at 8:20 am
Did you get the following error message for the second query
Msg 7601, Level 16, State 2, Line 5
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'schema.table' because it is not full-text indexed.
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 8:31 am
1) Check that the service "SQL Full-text Filter Deamon Launcher (SQL20XX)" is running. If not it must be running in order to use catalogues.
2)
Run this to see if you have any catalogs
select *
from sys.fulltext_catalogs
if there is no catalog, then run this:
use [db name]
create fulltext catalog FullTextCatalog as default
create fulltext index on [schema].[Table]([Column])
key index PK_key_name
and then try your second query.
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 8:45 am
Igor Micev (2/9/2016)
Did you get the following error message for the second query
Msg 7601, Level 16, State 2, Line 5Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'schema.table' because it is not full-text indexed.
No, no error. However wrong results.
I will try your other post.
February 9, 2016 at 8:47 am
Igor Micev (2/9/2016)
1) Check that the service "SQL Full-text Filter Deamon Launcher (SQL20XX)" is running. If not it must be running in order to use catalogues.2)
Run this to see if you have any catalogs
select *from sys.fulltext_catalogs
if there is no catalog, then run this:
use [db name]create fulltext catalog FullTextCatalog as default
create fulltext index on [schema].[Table]([Column])key index PK_key_name
and then try your second query.
The first query for checking if I have a FullTextCatalog returns 1 row.
fulltext_catalog_id: 5
name: Number_Name
path: NULL
is_default: 0
is_accent_sensitivity_on: 0
data_space_id: NULL
file_id: NULL
principal_id:1
is_importing:0
February 9, 2016 at 8:49 am
peter 67432 (2/9/2016)
Igor Micev (2/9/2016)
1) Check that the service "SQL Full-text Filter Deamon Launcher (SQL20XX)" is running. If not it must be running in order to use catalogues.2)
Run this to see if you have any catalogs
select *from sys.fulltext_catalogs
if there is no catalog, then run this:
use [db name]create fulltext catalog FullTextCatalog as default
create fulltext index on [schema].[Table]([Column])key index PK_key_name
and then try your second query.
The first query for checking if I have a FullTextCatalog returns 1 row.
fulltext_catalog_id: 5
name: Number_Name
path: NULL
is_default: 0
is_accent_sensitivity_on: 0
data_space_id: NULL
file_id: NULL
principal_id:1
is_importing:0
Keep on with the creation of the fulltext index
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 8:58 am
Keep on with the creation of the fulltext index
I've deleted the catalog and the index. and recreated based on your scripts.
Still same results though
February 9, 2016 at 9:01 am
why you have
where Contains(Name, '"Jo*"')
shouldn't it be
where Contains(Name, 'Jo')
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 9:05 am
Igor Micev (2/9/2016)
why you have
where Contains(Name, '"Jo*"')shouldn't it be
where Contains(Name, 'Jo')
When I do this, it has less results than when I use with the double quote and the *
February 9, 2016 at 9:07 am
I haven't worked with full text search much, but as I recall it doesn't support that sort of suffix search. It supports a prefix search, like you're already doing, but it cannot easily (or at all?) duplicate a LIKE '%term%' search.
Here's a CONNECT item that's relevant: https://connect.microsoft.com/SQLServer/feedback/details/758588/full-text-leading-wildcard-suffix-search. For pure suffix searches there are some workarounds with reversing the string and using a prefix search on that, but that doesn't cover all the same situations as a leading wildcard with LIKE.
My memory's a bit fuzzy on all this since I so rarely work with it, so I could be quite wrong, but based on some quick googling I don't think there's a good full-text alternative for LIKE '%term%'.
Cheers!
February 11, 2016 at 4:14 am
Jacob Wilkins (2/9/2016)
I haven't worked with full text search much, but as I recall it doesn't support that sort of suffix search. It supports a prefix search, like you're already doing, but it cannot easily (or at all?) duplicate a LIKE '%term%' search.Here's a CONNECT item that's relevant: https://connect.microsoft.com/SQLServer/feedback/details/758588/full-text-leading-wildcard-suffix-search. For pure suffix searches there are some workarounds with reversing the string and using a prefix search on that, but that doesn't cover all the same situations as a leading wildcard with LIKE.
My memory's a bit fuzzy on all this since I so rarely work with it, so I could be quite wrong, but based on some quick googling I don't think there's a good full-text alternative for LIKE '%term%'.
Cheers!
Looks like this indeed.
Do you maybe know if there are other way's to optimize this kind of statements?
February 14, 2016 at 5:06 pm
Short answer - what you want to do is not supported using Contains. You can get around it by doing a reverse search, but that would be a hack.
Read https://msdn.microsoft.com/en-us/library/ms187787.aspx for a description of CONTAINS.
For the hack, you add a new calculated column to the table being the REVERSE of the column to be searched, and then also index on that. You can then search using the term reversed. This is not recommended.
February 14, 2016 at 7:14 pm
If you're going to have to resort to such hacks as a "reverse" column especially for such a narrow column as a "Name" column, it's easier and better to simply split the column into a separate table as a bit of a poor man's full text index where you can use the full gambit of text search tools. It's what I did at work at I got very good performance out of it even with midstring searches simply because the column ends up being so very narrow.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2016 at 8:46 pm
Check out this article to see if an index on Name may help you get acceptable performance out of LIKE '%phrase%'
http://www.sqlservercentral.com/articles/SQL+LIKE+SEARCH/108015/[/url]
Short summary:
1. Add an index to your Name column.
CREATE INDEX s_ix1 ON dbo.Employee (Name);
2. Rewrite your query like this:
WITH Strings AS
(
SELECT ID, Name
FROM dbo.Employee
WHERE Name IS NOT NULL
)
SELECT Name
FROM dbo.Employee
WHERE Name LIKE '%Jo%';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2016 at 6:22 am
Jacob is correct. Contains doesn't work the same as Like. Like will find the character match based upon where the wild card characters - finding "TheJo' when the search phrase is %JO%. Contains is word based so it will find 'Jo', 'John' when the search phrase is JO*, but not 'TheJo' since it doesn't begin with JO. I've done a lot with this type of matching and LIKE give the most flexibility.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply