May 29, 2012 at 4:34 am
Hi,
I have a search stored procedure that basically performs a query similar to
select top 10 primary_key,field1,field2,field3
from Table1
where table1.dateField between @iDate1 and @iDate2 and exists
(select primary_key2 from table2
where table2.foreign_key = table1.primary_key and
table2.TextField LIKE '%'+@iKeyword+'%')
ORDER by table1.dateField desc
Basically table1 and table2 have of order 100,000 rows in them, and typically about 10% of those rows are included in the range @iDate1 to @iDate2. I have an index on table1.dateField descending and another index on table2.foreign_key. TextField is a varchar(15) and contains a (non-unique) business identifier.
Typically on my server hardware (running SQL 2008 R2 SP1 Standard) this query takes under a second to run. I have an issue that appears where @iKeyword is actually equal to TextField for a small proportion of the rows of table2(something less than 1% of the rows), and @iKeyword's length is within a few characters of the longest string stored in TextField and the first few characters of @iKeyword are the same as the first few chars on TextField for over 90% of the rows in table2. In these circumstances the query optimiser uses a query plan that is much less efficient for some reason - it takes of order 1 min 30 secs. Looking at the execution plan it seems to be using an index table2.TextField. SSMS is not picking up any missing indexes if I look at the missing_indexes DMV.
I thought that if there was a leading wildcard the query optimiser would not attempt to use an index for that field, but it appears I was mistaken.
I tried forcing the optimiser to re-compile specifically for that argument by adding 'WITH RECOMPILE' to the stored proc and re-executing. This had no effect on the execution time
I tried modifying the query to something like
;with FilteredTable1
as
( select Primary_key,field1,field2,field3,dateField
from table1
where table1.dateField between @iDate1 and @iDate2)
select top 10 f1.primary_key,f1.field1,f2.field2,f2.field3
from filteredtable1 as f1
where exists
(select primary_key2 from table2 inner join
filteredtable1 as f2 on table2.foreign_key = f2.primary_key
where table2.foreign_key = f2.primary_key and
table2.TextField like '%'+@iKeyword+'%')
order by f1.datefield desc
Unfortunately this didn't improve things either. The only way I that I have found to avoid this at this stage is to drop the index on table2.TextField. After I did this the second query above seems to run slightly faster than the first (ie faster than a second). As soon as I recreate the index on textfield1 the stored proc goes back to being 1min 30 secs execution time again for that specific argument. I would prefer to keep the index on textfield as it is being used for another (less frequent) query. I know that I could probably use an index hint to avoid this behaviour, but I generally consider this to be a last resort. I am beginning to think that I am at that 'last resort' stage. Does anyone have any ideas?
Thanks
Andrew.
May 29, 2012 at 4:55 am
Could you post the execution plan please?
Leading wildcard means SQL can't seek on an index. It can scan.
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
May 29, 2012 at 5:22 am
Have you considered using Full-Text?
May 29, 2012 at 5:30 am
The execution plans - one with ix_ProblemIndex takes 1 min 20 sec to run, if you drop problem index you end up with less than a second.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply