SQL Server Query Join taking very long when adding an additional column in the join

Question

I'm running a basic query on a table with a unique ID in each table, which is a non-clustered index in each. One table has 8 million rows, and the other has 800,000 rows.

When I run the following, it returns 24,000 rows in less than a second:

select     
    a.[ID]      
    ,b.[ID]    
from     
    dbo.tbl_1 a    
join     
    dbo.tbl_2 b    
on 
    a.unique_id = b.unique_id

However, when I add an additional column in the join which will significantly reduce the recordset, this takes about 8 minutes

select     
    a.[ID]      
    ,b.[ID]    
from     
    dbo.tbl_1 a    
join     
    dbo.tbl_2 b    
on 
    a.unique_id = b.unique_id 
    AND a.code_letter = b.code_letter

The "code_letter" column is just one letter, and is set to varchar(1). I'm scratching my head trying to understand why this is hanging. The issue is that I've run a dynamic sql insert query with 20,000 permutations of joins, and it's taking way too long.

Edit After trying this many ways, I realized that a simple select * seems to work very efficiently, while selecting specific columns is the culprit. Here is the execution plan when I select *:

Here is the execution plan when I select specific columns:

Again, my join is exactly the same, but the column selections are different.

I have, and that did not help for some reason.....but even if it did, it would be an annoying situation where I'd have to create an index for each iteration and drop it which could also be time consuming. — sqlnewbie1979
– sqlnewbie1979, Commented May 23, 2020 at 18:02
I'm wondering if it has something to do with cache-ing of the execution plan or something like that. It seems set off by the dynamic SQL that keeps runnign insert queries over and over with different columns in the join? — sqlnewbie1979
– sqlnewbie1979, Commented May 23, 2020 at 18:11
For performance questions, please show query plan from EXPLAIN. Is the 8 minutes the result of that single query by itself or your dynamic sql insert query process? if latter, show fuller TSQL code. — Parfait
– Parfait, Commented May 23, 2020 at 18:55
It's from the single query. This is in t-sql so I can't use explain. I bring up the dynamic part in case there's some reason to believe that would cause an unexpected issues. — sqlnewbie1979
– sqlnewbie1979, Commented May 23, 2020 at 20:09

sqlnewbie1979 · Accepted Answer · 2020-05-24 21:51:30Z

1

OP said, he didn't get the expected results, and based on his observations, I'll provide a different solution.

~~What I would do is, execute the following and get data to a temp table~~

select a.[ID] as aID ,b.[ID] as bID ,a.code_letter as aCode_letter ,b.code_letter as bCode_letter into #t from dbo.tbl_1 a join dbo.tbl_2 b on a.unique_id = b.unique_id

and then exec the following

Select aID, bID from #t Where aCode_letter = bCode_letter

select DISTINCT a.*    
into #ta
from     
    dbo.tbl_1 a    
join     
    dbo.tbl_2 b    
on 
    a.unique_id = b.unique_id 

select DISTINCT b.*    
into #tb
from     
    dbo.tbl_1 a    
join     
    dbo.tbl_2 b    
on 
    a.unique_id = b.unique_id

And exec

Select a.ID, b.ID 
from #ta a
Inner Join #tb b 
on a.unique_id = b.unique_id and a.Code_letter = b.Code_letter

edited May 24, 2020 at 21:51

sqlnewbie1979

1193 silver badges12 bronze badges

answered May 23, 2020 at 21:22

Srinika Pinnaduwage

1,0421 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

sqlnewbie1979 Over a year ago

I tried that and it was unfortunately time consuming. Here's the latest I've learned: When I run: Select * that seems to return results in seconds, whereas when I choose specific columns it spins for minutes. I've never seen that before, and it's rather unexpected behavior.

Srinika Pinnaduwage Over a year ago

Then why do't you do it like that. ie. Select * and then do the 2nd part separately? Or get a.* separately and b.* in to another temp tbl and then join those 2

sqlnewbie1979 Over a year ago

The issue there is just I'd have to do a lot of renaming of columns. I am adding the execution plan differences to the original issue if that helps to determine why it's executing so differently.

Srinika Pinnaduwage Over a year ago

please try my new suggestion

sqlnewbie1979 Over a year ago

That does work, although it seems like a workaround that should be avoidable. Thank you!

|

Collectives™ on Stack Overflow

SQL Server Query Join taking very long when adding an additional column in the join

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related