Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

In SQL Server 2008 R2 I have a query which looks like

create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select 
  id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art 
left outer join lager on art.id=lager.art_id and lager.skl_id=@skl_id
where sifra like '%'+@filter+'%' or naziv like '%'+@filter+'
end

I have an index on column naziv and column sifra.

I am considering changing this query to full text search. Table art has around 150K records and my main goal is to get faster response from SQL Server, because this query is common during daily work.

If I make a full text index on these two columns and redesign my query to use the full text index, what will happen with performance of this query?

share|improve this question
3  
Your LIKE queries with a leading wildcard are never going to use an index, so for all intents and purposes, you may as well pretend they don't exist. – Aaron Bertrand Feb 19 at 23:10
@AaronBertrand is there any other option to use any part of string inside search, beside using full text ? – adopilot Feb 20 at 12:01
none that I know of. Why are you afraid of full-text search? Just turn it on and try it. – Aaron Bertrand Feb 20 at 13:36
@AaronBertrand Perhaps I am afraid because I do not have enough knowledge about it. Ill try anyway thank you on nice comments. – adopilot Feb 20 at 13:47

1 Answer

up vote 2 down vote accepted

In large part it's going to depend on if @filter is a word or a group of words. Full text indexes essentially break down the contents of the column into the individual words and let you search for a word (group of words) or a synonym etc. 150k rows really isn't all that much to be searching on as these things go, but you may very well see some performance increase, again depending on what @filter is. I would also double check the rest of your indexes. For example one on art.id and one on lager.art_id and lager.skl_id. Also given how few columns you are returning (assuming that is the case in the real query not just this example) you might consider making them covering indexes by "including" (look at the key word INCLUDE in CREATE INDEX) the extra fields in your indexes. That lets the query just look at the index and not have to go back to the original table.

share|improve this answer
@filter in most time will be part of name (naziv) or part of catalog number (sifra). User knows only part of catalog number or part of name of an article. And then searching. Does that mean that it is no propose of setting full text indexes – adopilot Feb 20 at 12:02
That's my understanding of it. If I were you I would concentrate on performance on the rest of the query and minimizing the number of rows you are going to be checking with the likes. – Kenneth Fisher Feb 20 at 12:59
1  
If the part of the name or catalog number that is known can be deterministic in some way, you might consider storing that separately and searching on that instead of relying on partial match. For example if the name of a product is "big yellow boat" you could store big, yellow, and boat separately (think of them as "tags") and search those (using an index) instead of LIKE, giving priority to ones where more than one word matched. – Aaron Bertrand Feb 20 at 13:39
Also you can dramatically improve the performance of LIKE statements if you can get rid of the preceding wildcard. So field LIKE 'test%' is MUCH faster than field LIKE '%test%'. – Kenneth Fisher Feb 20 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.