CODEWITHSUNDEEP
sqlperformanceleft-join
Appel Flap
Appel Flap

Reputation: 271

SQL - LEFT JOIN takes an extremely long time to execute

I'm trying to see if there are any rows in table A which I've missed in table B.

For this, I'm using the following query:

SELECT t1.cusa
FROM patch t1
LEFT JOIN trophy t2
ON t2.titleid = t1.titleid
WHERE t2.titleid IS NULL

And the query worked before, but now that the trophy table has nearly 200.000 rows, it's extremely slow. I've waited 5 minutes for it to execute but it was still loading and timed out eventually.

Is there any way to speed this query up?

Upvotes: 0

Views: 468

Answers (2)

Jonathan Calindas
Jonathan Calindas

Reputation: 11

Adding Indexes to titleId on both tables (but especially t2) is the quickest way to get better performance. 200K records is nothing for SQL Server.

Upvotes: 1

Kartikeya Sharma
Kartikeya Sharma

Reputation: 1383

Try this and it might perform a bit better!

SELECT t1.cusa
FROM patch t1
WHERE NOT EXISTS (SELECT 1
    FROM trophy t2
    WHERE t2.titleid = t1.titleid );

Upvotes: 0

Related Questions