Have Geek Brain

More efficient left join of a large table

I have the following (simplified) request

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 a subquery, this procedure takes ~ 7 seconds, but with it it takes about 3 minutes.

Given that the table is peopleExtra

quite large, is there a more efficient way to make this join (without waiting for the DB restructuring)?

More details:

Inner part of a subquery like

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

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

The userTable contains 1500 entries.

+2


a source to share


3 answers


Your query is fine, just create the following indexes:

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


Rewriting the connection is not required (the optimizer SQL Server

can only handle nested queries), although it can make your query more human-readable.

+1


a source


how long does it take for this subquery to run on its own? If it takes about 3 minutes, you need to make the extra query more efficient for yourself - if it only takes a few seconds, then that's all the expression you need to work on.

Are there any indexes for people? Specifically starting at ElementID and including DataNumber? I suspect the problem is the join within your subquery causing the problem.



Also, can you include a query plan? Run SET SHOWPLAN_TEXT ON

in front of your query and then post the results here - this will help determine what is slowing it down.

+1


a source


Make a join to the table instead of a subquery, which should give the query preprocessor better freedom to create better 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
+1


a source






More articles:


All Articles