February 12, 2014 at 8:12 am
if you have wild card at front of pattern - the search cannot be optimised much, as full scan will be performed regardless of existing index, if any
If you have wild-card at the ind of search string, it may use index (if, you have one on a searchable column)
For real fast searches you better to use full-text index feature.
February 12, 2014 at 8:30 am
Thank you for your quick response, Eugene.
Regarding the full text index feature, can you please tell me how to use it? I have attempted at creating one, but it didn't help (it didn't even use the full text index). This is what I created:
CREATE FULLTEXT CATALOG [ft_catalog] ON FILEGROUP [FG1]
GO
CREATE FULLTEXT INDEX ON [table1]
([name] LANGUAGE 1033)
KEY INDEX [PK_table1_id] ON [ft_catalog]
GO
February 12, 2014 at 9:02 am
dajonx (2/12/2014)
Thank you for your quick response, Eugene.Regarding the full text index feature, can you please tell me how to use it? I have attempted at creating one, but it didn't help (it didn't even use the full text index). This is what I created:
CREATE FULLTEXT CATALOG [ft_catalog] ON FILEGROUP [FG1]
GO
CREATE FULLTEXT INDEX ON [table1]
([name] LANGUAGE 1033)
KEY INDEX [PK_table1_id] ON [ft_catalog]
GO
Your queries will need to be written specifically to take advantage of the full text index.
See this link:
February 12, 2014 at 9:05 am
That is not a small topic.
The best place to start from would be MS BoL:
http://technet.microsoft.com/en-us/library/ms142571.aspx
Try and play. When you will have some specific questions, I'm sure you will find a lot of help here.
February 12, 2014 at 9:23 am
Thank you, CK2.
How would I change the query to utilize CONTAINS if I'm just comparing the two [name] columns? In other words, if I'm thinking of it correctly, I would need to create a (dreaded) cursor to achieve this. There's no easy way to do it? I was hoping it would be something like:
SELECT b.id,
CAST(CASE WHEN EXISTS (SELECT a.id FROM table1 a WHERE CONTAINS(a.name, b.name) THEN 1 ELSE 0 END AS BIT) AS blah
FROM (SELECT TOP 2000 * FROM table2) b
(If this is correct, I'm not able to get it to work... Says Incorrect Syntax Near 'b'.)
February 12, 2014 at 10:06 am
You cannot use column name as predicate in Full-text searches.
Predicate must be 'freetext_string'
Which means, you should build it from your "table of predicates", then use in CONTAIN.
It is hard to say will it be faster in your case or not. You can test and you will know.
It might be that 2000 values in one search string is too much. Then you can do it in a cursor, again hard to say what will be faster. Really depends on size of searchable text.
I can see you columns names as "Name", so looks like they do not contain huge text. LIKE might be the right way to go. If the searchable text is large, you may find that full-text, even with using cursor, gives much better performance.
Please note: Full-text search is not the same as LIKE, you may find that you need to configure some lists of words to ignore, add some specific code for treatment of special characters (eg. - and &).
Looking into your query, I would just stick to LIKE.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply