title: SQL Left Join Decreasing Performance
body: <p>I was updating a report and was adding some data to a temp table using multiple left joins</p>
  <pre><code>CREATE TABLE #tSD(
                  PLCY00ID int,
                  CLSBSNS varchar(max),
                  ACTSD decimal(5,2),
                  NSWSD decimal(5,2),
                  NTSD decimal(5,2),
                  QLDSD decimal(5,2),
                  SASD decimal(5,2),
                  TASSD decimal(5,2),
                  VICSD decimal(5,2),
                  WASD decimal(5,2),
                  OSSD decimal(5,2),
                  NSWSDEx Varchar(250)
                  )
  
  
  INSERT INTO #tSD
  SELECT PLCY00ID=p.PLCY00ID,
      CLSBSNS=n100432.STRNGSMLVL,
      ACTSD=n100420.DCMLVL,       
      NSWSD=n100421.DCMLVL,   
      NTSD=n100422.DCMLVL,
      QLDSD=n100423.DCMLVL,
      SASD=n100424.DCMLVL,
      TASSD=n100425.DCMLVL,
      VICSD=n100426.DCMLVL,
      WASD=n100427.DCMLVL,
      OSSD=n100428.DCMLVL,
      NSWSDEx=Null--n100922.STRNGSMLVL
  FROM @tPLCYS p
  LEFT JOIN UC06DNDVLS0 n100432 ON n100432.PLCY00ID=p.PLCY00ID AND n100432.LMNT00ID=100432    
  LEFT JOIN UC06DNDVLS0 n100420 ON n100420.PLCY00ID=p.PLCY00ID AND n100420.LMNT00ID=100420
  LEFT JOIN UC06DNDVLS0 n100421 ON n100421.PLCY00ID=p.PLCY00ID AND n100421.LMNT00ID=100421
  LEFT JOIN UC06DNDVLS0 n100422 ON n100422.PLCY00ID=p.PLCY00ID AND n100422.LMNT00ID=100422
  LEFT JOIN UC06DNDVLS0 n100423 ON n100423.PLCY00ID=p.PLCY00ID AND n100423.LMNT00ID=100423
  LEFT JOIN UC06DNDVLS0 n100424 ON n100424.PLCY00ID=p.PLCY00ID AND n100424.LMNT00ID=100424
  LEFT JOIN UC06DNDVLS0 n100425 ON n100425.PLCY00ID=p.PLCY00ID AND n100425.LMNT00ID=100425
  LEFT JOIN UC06DNDVLS0 n100426 ON n100426.PLCY00ID=p.PLCY00ID AND n100426.LMNT00ID=100426
  LEFT JOIN UC06DNDVLS0 n100427 ON n100427.PLCY00ID=p.PLCY00ID AND n100427.LMNT00ID=100427
  LEFT JOIN UC06DNDVLS0 n100428 ON n100428.PLCY00ID=p.PLCY00ID AND n100428.LMNT00ID=100428
  LEFT JOIN UC06DNDVLS0 n100922 ON n100922.PLCY00ID=p.PLCY00ID AND n100922.LMNT00ID=100922
  </code></pre>
  <p>After I added the above the time required to run on a sample data increased from 3 seconds to 5 minutes.
  When I removed the insert and ran as a select only, the report ran for seconds again. I wasn't getting any duplication of rows.</p>
  <p>I then decided to split the left joins into individual queries. Something like this:</p>
  <pre><code>CREATE TABLE #tSD(
                  PLCY00ID int,
                  CLSBSNS varchar(max),
                  ACTSD decimal(5,2),
                  NSWSD decimal(5,2),
                  NTSD decimal(5,2),
                  QLDSD decimal(5,2),
                  SASD decimal(5,2),
                  TASSD decimal(5,2),
                  VICSD decimal(5,2),
                  WASD decimal(5,2),
                  OSSD decimal(5,2),
                  NSWSDEx Varchar(250)
                  )
  
  -- Class of Business
  INSERT INTO #tSD
  SELECT PLCY00ID=p.PLCY00ID,
      CLSBSNS=n100432.STRNGSMLVL,
      ACTSD=Null,     
      NSWSD=Null, 
      NTSD=Null,
      QLDSD=Null,
      SASD=Null,
      TASSD=Null,
      VICSD=Null,
      WASD=Null,
      OSSD=Null,
      NSWSDEx=Null
  FROM @tPLCYS p
  LEFT JOIN UC06DNDVLS0 n100432 ON n100432.PLCY00ID=p.PLCY00ID AND n100432.LMNT00ID=100432    
  
  
  UPDATE #tSD
  SET ACTSD=n100420.DCMLVL
  FROM @tPLCYS p
  LEFT JOIN UC06DNDVLS0 n100420 ON n100420.PLCY00ID=p.PLCY00ID AND n100420.LMNT00ID=100420
  
  
  UPDATE #tSD
  SET NSWSD=n100421.DCMLVL
  FROM @tPLCYS p
  LEFT JOIN UC06DNDVLS0 n100421 ON n100421.PLCY00ID=p.PLCY00ID AND n100421.LMNT00ID=100421
  </code></pre>
  <p>After I did the above the got improved to the seconds again.</p>
  <p>I'm just trying to understand what might have happened.</p>
  <p>Execution plan
  2 minutes
  <a href="https://i.stack.imgur.com/sct19.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sct19.png" alt="enter image description here" /></a></p>
  <p>15 seconds
  <a href="https://i.stack.imgur.com/KhZjP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KhZjP.png" alt="enter image description here" /></a></p>