473,378 Members | 1,407 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

When will a LIKE expression use an index?

I have a stored procedure that is taking a longer than is reasonable to execute, and I have isolated the part that is costing me the most. It is the part that uses a LIKE expression. Here is the sub query in question:

@partial_vernacular is the stored procedure parameter that contains the text I want to match on.

DECLARE @searchlike nvarchar(100);
SET @searchlike = @partial_vernacular +'%';

SELECT * FROM dbo.ls_vernacular_word_item_occurrances
WHERE word LIKE @searchlike

I have read that if a LIKE query uses the form WHERE term LIKE 'mytext%', that should prompt use of an index. Will that work in this situation where the text is passed in as a parameter? I can't see that this behaves any differently performance wise than when I set my search text this way:

SET @searchlike = '%' + @partial_vernacular +'%';

The [word] field is defined as varchar(50). Does the field data type have any bearing on this?
Mar 15 '10 #1
2 1179
2,878 Expert 2GB
Is your [WORD] column in your index?

~~ CK
Mar 15 '10 #2
Yes, the table does have an index on the column in question. The table contains about 100,000 records, and since recently adding the LIKE expression to the procedure, I have lost about half a second to a second (this is part of an auto-suggestion feature, which is why the speed is important to me).
Mar 16 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
by: PengYu.UT | last post by:
I'm wondering when I should use ptrdiff_t and size_t instead of int and unsigned int or long and unsigned long? Is there any guideline I should follow? Peng
by: Dima Tkach | last post by:
Hi, everybody! I just ran into a weird problem on 7.3.4. Here is a simple testcase: rapidb=# create table nametab (name text); CREATE TABLE rapidb=# create index name_idx on nametab(name);...
by: Debashish Chakravarty | last post by:
K&R pg.66 describes two situations when using goto makes sense. Has anyone here come across situations where using goto provided the most elegant solution. --...
by: PengYu.UT | last post by:
Hi, Sometimes, I write = instead of == in if conditions by mistakes. Is there any way the gcc compiler can give me a warning? Best wishes, Peng
by: JohnR | last post by:
From reading the documentation, this should be a relatively easy thing. I have an arraylist of custom class instances which I want to search with an"indexof" where I'm passing an instance if the...
by: Bob Greschke | last post by:
I miss being able to do something like this in Python 1f (I = a.find("3")) != -1: print "It's here: ", I else: print "No 3's here" where I gets assigned the index returned by find() AND the...
by: a | last post by:
I'm having trouble testing a custom object. I've tried many different approaches. One is shown below. The XML below shows the state of the object and I'm trying to test for that state, ie there...
by: Mercy | last post by:
I guess my C++ is pretty darn rusty. I was just looking over sample C++ code for practice... and I'm kind of confused about this code fragment: int sector2; int i = 3; memset(sector2,...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.