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

Question

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.

Answer 1

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).

Answer 2

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.

Answer 3

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.