March 18, 2011 at 7:42 am
I've a very complex query on which I'm working from past 2-3 weeks. Had already done lots of experiments. I've broken down this query to a part where its taking too much time [Out of 4 secons, 3 secs are taken by this part]. Its a select statement on a big table with over 2000000 records. Here we have defined index but since we are using LIKE operator with '%<SearchString>%', its doing an entire scan and thus taking long time. Is there any solution for this problem ?
March 18, 2011 at 7:52 am
Ya, find ways to avoid doing that search. OR find other conditions that you could add that could use a filter.
OR do it like so where col like 'search%'
March 18, 2011 at 8:01 am
Yeah I understand, but its not possible to change my requirement. One more thing, the clustered index on that table consists of four columns and my WHERE clause consists of two of them [numbered two and three in the index]. The second column in my WHERE clause is using the LIKE operator.
March 18, 2011 at 8:05 am
Any way you can use the 1st column in the where??
You might consider making an extra index with column 3, 2 in that order.
Assuming column 3 is selective, that could help.
Now I'm just guessing from this point on because I don't have enough info to go on, but you could make that index covering by including the rest of the columns you need. Can't confirm without knowing more about the table, data, other indexes, usages... etc.
March 18, 2011 at 8:18 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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 18, 2011 at 8:57 am
No working. Here is the table structure:
BusinessDate datetime no 8 no (n/a) (n/a) NULL
PName varchar no 30 no no no SQL_Latin1_General_CP1_CS_AS
DName varchar no 30 no no no SQL_Latin1_General_CP1_CS_AS
Status varchar no 20 yes no yes SQL_Latin1_General_CP1_CS_AS
StartTime datetime no 8 yes (n/a) (n/a) NULL
EndTime datetime no 8 yes (n/a) (n/a) NULL
ElapsedTime int no 4 10 0 yes (n/a) (n/a) NULL
HName varchar no 100 yes no yes SQL_Latin1_General_CP1_CS_AS
UName varchar no 100 yes no yes SQL_Latin1_General_CP1_CS_AS
RNum smallint no 2 5 0 no (n/a) (n/a) NULL
IsLatest char no 1 yes no yes SQL_Latin1_General_CP1_CS_AS
StartTimeInUTC datetime no 8 yes (n/a) (n/a) NULL
EndTimeInUTC datetime no 8 yes (n/a) (n/a) NULL
AppUserName varchar no 100 yes no yes SQL_Latin1_General_CP1_CS_AS
Clustered Index structure:
PK_Table clustered, unique, unique key located on PRIMARY BusinessDate, PName, DName, RNum
My Query:
;WITH Max_ReportStatus
AS (
SELECT BusinessDate,PName,DName,BName,SName,AName,RName,
RerunCount,RNum,Status,
MAX(RerunCount) OVER (PARTITION BY BusinessDate,PName,DName,BName,
SName,AName, RName,RNum) as MaxRerunCount
FROM FINRISK.dbo.T_REPORTSTATUS (NOLOCK)
WHERE PName = 'MainProfile'
AND DName LIKE '%SearchPattern%')
SELECT PName, DName, BusinessDate, RNum, Status, COUNT(Status) StatusCount
FROM Max_ReportStatus
WHERE RerunCount = MaxRerunCount
GROUP BY PName, DName, BusinessDate, RNum, Status, RerunCount, MaxRerunCount
March 18, 2011 at 9:04 am
Execution plan?
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 18, 2011 at 9:09 am
On top of the exec plan, what other indexes do you have on the table?
March 18, 2011 at 9:13 am
Also have you considered using Full Text search instead of using Like ?
It could be worth a test.
March 18, 2011 at 9:28 am
Here it is.
March 18, 2011 at 9:41 am
Any other indexes on the table?
March 18, 2011 at 10:01 am
Nope. Just this one.
March 18, 2011 at 10:25 am
Well, the execution plan is suggesting that you can get a 70% improvement by adding this index...
USE [FINRISK]
GO
CREATE NONCLUSTERED INDEX [IX_T_REPORTSTATUS_Cover] -- rename as appropriate
ON [dbo].[T_REPORTSTATUS] ([ProfileName],[DomainName])
INCLUDE ([COBDate],[BookName],[SystemName],[AdapterName],[ReportName],[RerunCount],[Status],[RunNumber])
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 18, 2011 at 10:29 am
WayneS (3/18/2011)
Well, the execution plan is suggesting that you can get a 70% improvement by adding this index...
USE [FINRISK]GO
CREATE NONCLUSTERED INDEX [IX_T_REPORTSTATUS_Cover] -- rename as appropriate
ON [dbo].[T_REPORTSTATUS] ([ProfileName],[DomainName])
INCLUDE ([COBDate],[BookName],[SystemName],[AdapterName],[ReportName],[RerunCount],[Status],[RunNumber])
Agreed but you add an index wih 10 of the 14 columns in the table, and then you need to add the 4 keys so you create a new index with 100% of the columns in the table.
That means 2 things, either rethink the clustered index or forget about those 3 seconds.
March 18, 2011 at 10:51 am
Understand but how can we change any clustered index based on just one query. We have created that index based on various facts. This index has been created based on selectivity of many other queries and giving good results there. Its alos not about creating new index i think. As I've read somewhere that LIKE operator never takes help of any index and in any ways it scans the entire table. Can we beat this problem anyhow ?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply