CODEWITHSUNDEEP
sql-servert-sql
Arthur
Arthur

Reputation: 3473

Nested Loops performance issue on a very simple query

I have a very simple table and a very simple INNER JOIN query and a huge count of rows.

IF OBJECT_ID('tempdb..#blackIPAndMACs') IS NOT NULL
    DROP TABLE #blackIPAndMACs
       
CREATE TABLE #blackIPAndMACs
(
       ResourceID     dsidentifier,
       MACAddress     VARCHAR(500),
       IPAddress      VARCHAR(50)
)

CREATE INDEX #blackIPAndMACs_idx1 ON #blackIPAndMACs(MACAddress)
CREATE INDEX #blackIPAndMACs_idx2 ON #blackIPAndMACs(IPAddress)
CREATE INDEX #blackIPAndMACs_idx3 ON #blackIPAndMACs(MACAddress, IPAddress)
CREATE INDEX #blackIPAndMACs_idx4 ON #blackIPAndMACs(ResourceID)

After this table has been filled with 2.514.000 rows, I am trying to find all ResourceID, that accessed from similar IP or MAC:

SELECT b1.*,
       b2.*
FROM   #blackIPAndMACs b1 with(NOLOCK, INDEX=#blackIPAndMACs_idx3) 
       INNER JOIN #blackIPAndMACs b2 with(NOLOCK, INDEX=#blackIPAndMACs_idx3)
            ON  (
                    b1.MACAddress = b2.MACAddress
                    OR b1.IPAddress = b2.IPAddress
                )    
WHERE  1 = 1

As a result, this query executes (possible) infinitely. Our server is really powerful. I think I can't disclose this information, but I can only say, that the RAM of the server counts in a lot of hundreds of GB.

enter image description here

What kind optimization should I use to speedup the query execution?

Update 1:

OK, I removed OR and changed SELECT to count (b1.ResourceID), but it didn't solve the issue. Even such simple query executes too long:

SELECT count (b1.ResourceID)
FROM   #blackIPAndMACs b1 with(NOLOCK) 
       INNER JOIN #blackIPAndMACs b2 with(NOLOCK)              
                    b1.MACAddress = b2.MACAddress
WHERE  1 = 1
AND b1.ResourceID != b2.ResourceID

enter image description here

Upvotes: 0

Views: 285

Answers (1)

Thailo
Thailo

Reputation: 1424

As a force of habit I would refrain from using select *, even if you need a whole bunch of fields as a result. Having said that my approach would be something like this:

SELECT
          b1.ResourceID
        , b2.MACAddress
        , b2.IPAddress
        , b3.MACAddress
        , b3.IPAddress
FROM      #blackIPAndMACs AS b1
LEFT JOIN #blackIPAndMACs AS b2 ON b1.MACAddress = b2.MACAddress
LEFT JOIN #blackIPAndMACs AS b3 ON b1.IPAddress = b2.IPAddress;

Which uses a much more efficient query plan:

enter image description here

Upvotes: 2

Related Questions