How to ensure that my inner joins don't block each other

saanah khan 21 Reputation points
2021-07-09T22:26:00.71+00:00

I have a basic query that is returning the unique CharacterID for the the different programs

Select Distinct CharacterId
From dbo.Character c

inner join (select character Id from programa ) on a.characterid = c.characterid
inner join (select character Id from programb ) on b.characterid = c.characterid
inner join (select character Id from programc ) on c.characterid = c.characterid
inner join (select character Id from programd ) on D.characterid = c.characterid
inner join (select character Id from programe ) on e.characterid = c.characterid
inner join (select character Id from programf ) on f.characterid = c.characterid
inner join (select character Id from programg ) on g.characterid = c.characterid
inner join (select character Id from programh ) on h.characterid = c.characterid

The issue is that when I run the query seperately for each program, each runs in less than a second. WHen I run it as above, they block each other and run for 10+ minutes. What is the best way to run the above query so that it runs fast and doesn't block each other.

This is a view above. I tried turning into a SP and using temptables for each inner join but that didn't work either.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,685 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-07-10T07:40:25.827+00:00

    They don't block each other. But a query with ten 10 tables is more complex than a query with a single table. Also, you may be doing something wrong so that you cause a row explosion.

    In order to help you, we need to have more information. To start with, we need to see the actual query you are using. (The query you have posted is not syntactically valid, so it is not of much help to us.) It also helps to have the actual query plans (in XML format).

    0 comments No comments

  2. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-07-12T09:38:17.247+00:00

    Hi @saanah khan ,

    It seems you want to match the rows between ten tables. If misunderstood, please let me know.

    The query that you offered has an incorrect syntax near the keyword ‘on’.

    The query may as below;

    Select Distinct CharacterId  
    From dbo.Character c  
      
    inner join program a  on a.characterid = c.characterid  
    inner join program b on b.characterid = c.characterid  
    inner join program c on c.characterid = c.characterid  
    inner join program d on D.characterid = c.characterid  
    inner join program e on e.characterid = c.characterid  
    inner join program f on f.characterid = c.characterid  
    inner join program g on g.characterid = c.characterid  
    inner join program h on h.characterid = c.characterid  
    

    For SQL query running slow, suggest you to look at the execution plan for the query and see if there are any glaring problems as Erland mentioned.


  3. Jeffrey Williams 1,886 Reputation points
    2021-07-12T22:12:13.773+00:00

    I think all you need is to check for the existence:

     Select --Distinct 
            c.CharacterId
       From dbo.Character c
      Where Exists (Select * From programa pa Where pa.CharacterID = c.CharacterID)
       And Exists (Select * From programb pb Where pb.CharacterID = c.CharacterID)
       And Exists (Select * From programc pc Where pc.CharacterID = c.CharacterID)
       And Exists (Select * From programd pd Where pd.CharacterID = c.CharacterID)
       And Exists (Select * From programe pe Where pe.CharacterID = c.CharacterID)
       And Exists (Select * From programf pf Where pf.CharacterID = c.CharacterID)
       And Exists (Select * From programg pg Where pg.CharacterID = c.CharacterID)
       And Exists (Select * From programh ph Where ph.CharacterID = c.CharacterID)
    

    The DISTINCT shouldn't be needed, unless Character has multiple rows for each CharacterID, which doesn't make sense. There may be ways to optimize this - but it should work.

    0 comments No comments