Update with Joins taking too long

Question

the below queries return about 80K records:

this select only takes about 2 seconds to come back:

select fs.fsID
from datFS fs
join datAE t2
on fs.fsID= t2.fsID
join @AllCompletedNotYetDeleted t1
on fs.fsID=t1.fsID
where DateSent < DATEADD(m, -6, GETDATE())

while his update (with the exact same joins) is taking forever... i have to stop it every time, so it is yet to finish...

update fs set fs.testrun = getdate()  
from datFS fs
join datAE t2
on fs.fsID= t2.fsID
join @AllCompletedNotYetDeleted t1
on fs.fsID=t1.fsID
where DateSent < DATEADD(m, -6, GETDATE())

what did i do wrong?

Add an `order by` clause or `select count(*)` on the first query. The time to return the first record is not the time to return all of them. — Gordon Linoff, Jan 19 '16 at 15:57
for performance suggestion you have to provide the explain query http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Juan Carlos Oropeza, Jan 19 '16 at 15:57
@GordonLinoff - yes, thank you, with the order by, it returned 8K in 1 minute, which is a while... is there anything else i can do to improve performance, even on the select? — Madam Zu Zu, Jan 19 '16 at 16:01
@MadamZuZu - You can add an index on `DateSent` as mentioned in my comments of the first answer. — JonH, Jan 19 '16 at 16:06
To what table does `DateSent` belong? Besides indexing that, I'd recommend refactoring the table variable as a temp table so that you can create indexes on it and SQL Server can keep statistics of it. — Dan Field, Jan 19 '16 at 16:18
You need to consider that it is having trouble acquiring locks. Is datFS a busy table. — paparazzo, Jan 19 '16 at 16:27
You are joining with a TABLE variable... You should add `OPTION(RECOMPILE)` to tell to the compiler to recompile and take the number of rows in the TABLE variable into account when constructing the execution plan. — TT., Jan 19 '16 at 17:44

score 1 · Answer 1 · answered Jan 19 '16 at 16:17

Try this one -

CREATE UNIQUE NONCLUSTERED INDEX ix ON dbo.datFS (DateSent, fsID)
GO

DECLARE @dt DATETIME = DATEADD(M, -6, GETDATE())

UPDATE fs
SET fs.testrun = GETDATE()
FROM datFS fs
WHERE DateSent < @dt
    AND fs.fsID IN (
        SELECT t1.fsID
        FROM @AllCompletedNotYetDeleted t1
        JOIN datAE t2 ON t1.fsID = t2.fsID 
    )
OPTION(RECOMPILE)

Fuzzy · Answer 2 · 2016-01-19T15:57:19.243

0

Try this:

DECLARE @date datetime = getdate()

update fs set fs.testrun = @date 
from datFS fs
join datAE t2
on fs.fsID= t2.fsID
join @AllCompletedNotYetDeleted t1
on fs.fsID=t1.fsID
where DateSent < DATEADD(m, -6, @date)

You will be calling the date function only once rather than for each row you update.

edited Jan 19 '16 at 15:57

answered Jan 19 '16 at 15:54

Fuzzy

3,810
2
15
33

Would it make sense to add an index on DateSent? – JonH Jan 19 '16 at 15:57
Adding an index would help in this case but it will slow down updates on the DateSent column in the future. I for one am for indexing as more often than not it improves read performance, but its not always the solution as it slows down updates and inserts. – Fuzzy Jan 19 '16 at 15:59
Then I would suggest what @JonH suggested and if you are using SQL Server management studio you can run an execution plan and see what indexes it recommends. – Fuzzy Jan 19 '16 at 16:04
1

getdate() is very very fast – paparazzo Jan 19 '16 at 16:56

paparazzo · Answer 3 · 2016-01-19T16:43:57.517

Please run this and post the execution plan
The reason for the top 10 is just to let it get done and eliminate transaction log filling up / slow as a problem
You do NOT want to leave the with (nolock) active - this is just to test for lock contention

I trust fsID is PK on datAE ??

Declare fsID as PK on @AllCompletedNotYetDeleted
If that is large even try it as #temp
The query optimizer can do poorly with table variables

Rebuild indexes if they are fragmented

update  fs 
set top (10) fs.testrun = getdate()  
from datFS fs with (rowlock)
join datAE t2 with (nolock)
     on fs.fsID = t2.fsID
join @AllCompletedNotYetDeleted t1
     on fs.fsID = t1.fsID
where DateSent < DATEADD(m, -6, GETDATE())

score 0 · Answer 4 · edited May 23 '17 at 12:15

The answer to your question is that you want indexes. For this query:

select fs.fsID
from datFS fs join
     datAE t2
     on fs.fsID = t2.fsID join
     @AllCompletedNotYetDeleted t1
     on fs.fsID = t1.fsID
where DateSent < DATEADD(month, -6, GETDATE());

The best indexes are (probably) datAE(fsID) and @AllCompletedNotYetDeleted(fsID). In SQL Server 2014, you can create an index explicitly on a table variable. In earlier versions, you can do so implicitly by creating a unique constraint. This answer is helpful.

An index including DateSent might be useful. However, it is unclear what table the column is in, and the where condition might be selecting so many rows that the index would not be used.

Finally, you should also check if you are getting duplicate records in the select/update -- such duplicates just waste time in an update.

Update with Joins taking too long

4 Answers4