CODEWITHSUNDEEP
sql-server-2008-r2
Nick K
Nick K

Reputation: 389

SQL Server 2008 R2 count of rows takes more time when returning values

I have a stored procedure which uses a view. The stored procedure accepts various parameters.

I have tried:

SELECT 
    COUNT(v.CampaignID) as RecordCount
FROM 
    VW_Results_ValueRank_2010_12_22_NEW V                        
INNER JOIN 
    ResultTopic ON V.ResultID = ResultTopic.ResultId    
INNER JOIN 
    CampaignTopic on ResultTopic.topicid = CampaignTopic.topicid         
WHERE 
    v.CampaignID = 37                        
    AND v.CreateDate BETWEEN 'May  3 2011  8:25PM' AND 'May  3 2012  8:25PM'                        
    AND v.SourceDate BETWEEN DATEADD(d,-3,'May  3 2011  8:25PM') AND 'May  3 2012  8:25PM' 
    AND (LEFT(Title, 80) LIKE '%google%' OR Domain LIKE '%google%' OR LEFT([Text], 300) LIKE '%google%')

It returns 2016 records but takes 40-50 sec, can I decrease this time to 5-10 sec. Please help.

Upvotes: 2

Views: 464

Answers (1)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

You can check for missed index by looking at execution plan and create them.

If all indexex are there and no index can be create on table then you can create index view for CreateDate , SourceDate and other columns in where clause.

refer this link

Upvotes: 2

Related Questions