CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Chris
Chris

Reputation: 28064

SQL Server 2005 charindex performance

I'm trying to run an update query that updates one table based on rows it finds in another table where a specific value exists anywhere in one of the remote table's column values. The query I'm using is:

update 
    c
set
    LastStep = @StepNumber,
    LastDate = pv.Created
from
    @Conversions c
        inner join PageViews pv on c.SessionID = pv.SessionID
where
    c.GoalName = @GoalName AND
    pv.Created > c.LastDate AND
    charindex(@MatchValue, pv.PageUrl) > 0;

On a test database with 50,000 rows in the PageViews table, this single query is causing just over 1 million reads according to SQL profiler and is taking 1 minute and 14 seconds. Any ideas why?

Upvotes: 0

Views: 1069

Answers (1)

Jonas Lincoln
Jonas Lincoln

Reputation: 9767

Do the following column have indexes? pv.SessionID, pv.created

Have you looked at the resulting execution plan?

Upvotes: 1

Related Questions