CODEWITHSUNDEEP
sqlsql-server-2008t-sqldatabase-performance
user3827216
user3827216

Reputation: 33

Update with join takes long time

I have a problem updating a table. I'm running the following query:

UPDATE Table1 SEt entrena = c.Count 
FROM Table1 AS p INNER JOIN (
SELECT e.EmplID, COUNT(e.EmplID ) as Count 
FROM Table2 AS e WHERE e.Start >= @Start AND e.Start <=@End 
GROUP BY e.EmplID ) AS c ON p.EmplID = c.EmplID
WHERE P.Date  = '2050-12-31'

The Table1 has 12000 rows and the select inside the join get only 51 rows but the update takes around 2 minutes, but if I delete the where clase p.date = '2050-12-31' the update takes less than a second. And I can't figure out how to solve it. I'm using SQL Server 2008.

Both tables don't have indexes.

Upvotes: 2

Views: 1859

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is your query:

UPDATE Table1
    SET entrena = c.Count 
    FROM Table1 p INNER JOIN
         (SELECT e.EmplID, COUNT(e.EmplID ) as Count 
          FROM Table2 e
          WHERE e.Start >= @Start AND e.Start <=@End 
          GROUP BY e.EmplID
         ) c
    ON p.EmplID = c.EmplID
    WHERE P.Date  = '2050-12-31';

First, you need to change the first line to:

UPDATE p

when you define an alias in the from clause, you need to use that in the update for the right thing to happen. (I wish your query generated an error in SQL Server, but it does not.)

To optimize this query, you want to add indexes. I would suggest these two:

Table1(date, EmplID)
Table2(EmplId, Start)

You may then find that the correlated subquery version is faster, particularly if the where clause is highly selective:

UPDATE p
    SET entrena = (SELECT COUNT(e.EmplID ) as Count 
                   FROM Table2 e
                   WHERE e.Start >= @Start AND e.Start <=@End AND
                         p.EmplID = e.EmplID
                  )
    FROM Table1 p
    WHERE P.Date  = '2050-12-31';

However, I suspect that the p versus table1 is the root of your performance problem.

Upvotes: 6

Related Questions