INNER JOIN slowing down the query

Question

I have an inner join like so:

INNER JOIN Area AS area ON (Area.Id = psaLocalityOrCity.AreaId OR Area.Id = psaDbl.AreaId)

psaLocalityOrCity are locations that I need areas for, however there are some records that are duplicated in psaLocalityOrCity. These, and only these, have been extracted into psaDbl as single records. INNER JOIN above is suppose to find me all areas for all localities in both groups, but this OR statement is causing it to run for ages. What would be a reason for that? Is there any fix that would optimize the query to run for 6sec again instead of 3min?

INNER JOIN with `OR` no wonder it is slow, See Execution Plan and check it uses indexes or execute full table scan — Lukasz Szozda, Sep 02 '15 at 10:30
Show all query, try `WITH cte AS (SELECT * FROM psaLocalityOrCity UNION ALL SELECT * FROM psaDbl) SELECT * FROM tab JOIN cte`, replace * with column names of course — Lukasz Szozda, Sep 02 '15 at 10:36
check if types of that ids is the same. implicit casting is the #1 cause of slowness in those cases. Cause #2 is bad or lack of indexes — jean, Sep 02 '15 at 11:07

score 2 · Answer 1 · answered Sep 02 '15 at 10:43

INNER JOIN with OR is going to result in a nested loop join, the slowest kind. One method is to use UNION ALL, as suggested in a comment. Another is to use two LEFT JOINs. An example of the structure:

SELECT . . .,
       COALESCE(aloc.col1, adbl.col1)
FROM psaLocalityOrCity loc LEFT JOIN
     Area aloc
     ON aloc.Id = loc.AreaId LEFT JOIN
     Area adbl
     ON adbl.Id = psaDbl.AreaId
WHERE aloc.id is not null or adbl.id is not null

Note: this will not work if multiple rows in Area match each column. However, that seems unlikely given that the match is on a column called id.

paparazzo · Answer 2 · 2015-09-02T11:03:02.137

Or in join is often slow
The query optimizer goes into a loop join

JOIN Area AS area 
  ON Area.Id = psaLocalityOrCity.AreaId 
  OR Area.Id = psaDbl.AreaId

Not the same as now you have two AREA to deal with
But you know where the area came from
May result in more rows but would be faster
The answer from Linoff is better

LEFT JOIN Area AS AreaCity 
  ON AreaCity.Id = psaLocalityOrCity.AreaId 
LEFT JOIN Area AS AreaDpl 
  ON AreaDpl.Id  = psaDbl.AreaId

score -1 · Answer 3 · answered Sep 02 '15 at 10:46

-1

There are some way to improve performance of this type of queries.(But I am not sure whether it will work for you or not.)

following can be done to improve performance:

1. Use WITH (NOLOCK) in all the tables
2. If you are using the query many times a day then try create a cache table, i.e populate the result in a table and use select statement to retrive result from the table always.
3. Put only the required columns in the select statement.

answered Sep 02 '15 at 10:46

Biswabid

1,378
11
26

using **nolock** sometimes is advisable but can se considered a bad practice if used too often. Most times it's a patch solution on the symptoms and hides the real problem – jean Sep 02 '15 at 11:06
Its worth giving a shot. :) – Biswabid Sep 02 '15 at 11:08

score -1 · Answer 4 · edited May 23 '17 at 11:44

-1

Sorry, got this resolved by combining psaLocalityOrCity and psaDbl into one, using SELECT with UNION. This allowed me to eliminate OR from INNER JOIN and query performs much better now. Got the idea from here: Is having an 'OR' in an INNER JOIN condition a bad idea?

Thanks for all your suggestions

edited May 23 '17 at 11:44

Community

1
1

answered Sep 02 '15 at 11:04

Bartosz

4,542
11
43
69

INNER JOIN slowing down the query

4 Answers4