More efficient left join of big table

Question

Hello, I have the following (simplified) query

select P.peopleID, P.peopleName, ED.DataNumber
from peopleTable P
    left outer join (   
        select PE.peopleID, PE.DataNumber 
        from formElements FE
             inner join peopleExtra PE on PE.ElementID = FE.FormElementID 
        where FE.FormComponentID = 42
    ) ED on ED.peopleID = P.peopleID

Without the sub-query this procedure takes ~7 seconds, but with it, it takes about 3minutes.

Given that table peopleExtra is rather large, is there a more efficient way to do that join (short of restructuring the DB) ?

More details:

The inner part of the sub-query, e.g.

  select PE.peopleID, PE.DataNumber 
        from formElements FE
             inner join peopleExtra PE on PE.ElementID = FE.FormElementID 
        where FE.FormComponentID = 42

Takes between <1 and 5 seconds to execute, and returns 95k rows

There are 1500 entries in the peopleTable.

Answer 1

how long does that sub-query take to run by itself? If it takes close to 3 minutes, then you need to make the sub-query more effecient on its own - if it takes only a few seconds, then it's the whole statement that needs to be worked on.

Are there any indexes on peopleExtra? Specifically, on that starts with ElementID and includes DataNumber? I suspect the problem is the join inside your subquery that's causing trouble.

Also, can you please include a query plan? Run SET SHOWPLAN_TEXT ON before your query and then post the results here - that will help determine what's slowing it down.

Answer 2

Make a join against the table instead of a subquery, that should give the query preprocessor better freedom to make the best joins.

select p.peopleID, p.peopleName, pe.DataNumber
from peopleTable p
left join (
  formElements fe
  inner join peopleExtra pe on pe.ElementID = fe.FormElementID
) on pe.peopleID = p.peopleID
where fe.FormComponentID = 42

Answer 3

Your query is OK, just create the following indexes:

PeopleExtra (PeopleID) INCLUDE (DataNumber, ElementID)
FormElements (FormComponentID, FormElementID)

Rewriting the join is not required (SQL Server's optimizer can deal with the nested queries just fine), though it can make your query more human-readable.