March 2, 2015 at 11:27 pm
Hi All,
i am using the query
select id From Job where Title like '%java%'
it will take 10 sec time
after that i am using the Force Index Query Hints
like
select id From Job WITH (INDEX(ind_job_Title)) where Title like '%java%'
it is also getting same time there is no improvement
please help me how to use Force Index
OR
how to impove the query performance tips
March 3, 2015 at 1:22 am
polo.csit (3/2/2015)
Hi All,i am using the query
select id From Job where Title like '%java%'
it will take 10 sec time
after that i am using the Force Index Query Hints
like
select id From Job WITH (INDEX(ind_job_Title)) where Title like '%java%'
it is also getting same time there is no improvement
please help me how to use Force Index
OR
how to impove the query performance tips
Quick thought, consider re-writing the query as the leading wildcard in the predicate will render the index useless.
😎
March 3, 2015 at 9:48 am
Index hints aren't likely to help here.
Have you considered full-text indexing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2015 at 8:42 am
As previous post indicated you will not get it to do an index seek. Best it will be able to do is index scan.
If your table is wide (lots of columns) and has lots of rows then best you can do for this exact requirement (returning only id column) is a full scan of a non-clustered index contaning both the title and id columns. If Id is clustered index then it will automatically be embedded in the index. This will result in less data being munched by the query as it reads though a relatively small strucure rather than scanning the full table.
Fulltext indexes might work but be careful if single letters are used in data as default fulltext does not index them. Remove them from fulltext stoplist or have your fulltext not use stoplist (stoplist=off) which is a list of words fulltext should ignore. Fulltext does not work so well with like style queries but has other benefits like dictionaries for similar sounding words and good stuff like that.
Another option is to create a Title table with an primary key and use that primary key in your data or just use the table as a lookup for values in the main table. Then your query can search the Title table for all possible values matching you like clause and then use an indexed search on ther big table for those values.
March 6, 2015 at 10:30 am
I've found it's general advisable to avoid index hints almost always.
Indexes change, data changes, you might be forcing something to run in a way that used to be appropriate when you carried out the initial data load, but has become less and less useful over time as things have evolved.
It's a special day indeed when I decide that an index hint is the BEST way to run a query.
March 11, 2015 at 3:40 pm
I agree with EMarkM that 99.99% of the time its a mistake to force use of an index. Not to be unkind, but the optimizer knows what it is doing better than you do, especially given that you obviously don't understand how indexes work with % searches. So it might help you a bit to understand that first.
Indexes are sequenced in alphabetic or numeric order according to the specifications in the Create Index statement. That means if you create an index on last name, first name, the lastname 'Adams' will appear in the index before the name Brown. First name is merely a tie breaker for sequencing rows that have the same last name, so obviously if you are doing a search on first name, not last name, the sequence of the index will do you no good.
Similarly, when you go looking for a word like 'Java' in the primary column of an index, the optimizer uses the index structure to navigate quickly to that exact word. This is known as an index seek and it is fast. If you were searching for rows like 'JAVA%', with no leading % sign, the optimizer will pick the most convenient index, (usually the one with the right sequence) to get there with a minimum amount of work.
However, when you search for rows using like string %java%, you are saying that "java" can appear anywhere in the string. Ajava, Bjava, ZZTOPjavathehut, would all returned by such a search. So the sequence of an index is really no help to such a search. The optimizer has no choice but to look at EVERY row and filter out the ones that don't match. This is called an index scan. When called upon to do an index scan, the optimizer will probably choose the smallest index containing that column, whether indexed on it or not, because it can read through a smaller index faster. Forcing it to use a larger index with what you think is the correct sequence will actually slow down the unavoidable index scan.
I respectfully suggest that you forget about forcing indexes and instead spend some time understanding how indexes work, and the implications of using LIKE searches that begin with a '%'. There are a wealth of articles online about indexes. It will pay you huge dividends in the future to understand more about them now.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply