title: SQL LEFT JOIN and SUM() slow
body: <p>I'm running a query for calculating the total length of an object.
  The object is made out of several smaller segments(from a different table).
  Running queries with a single segment for each object works fine, but the load gets really slow for adding more segments. (3000 objects and 9000 segments).</p>
  <p>Below is the relevant part of my query:</p>
  <pre><code>SELECT
      t1.ObjectID,
      SUM(SegmentLength) AS TotalLength
  
  FROM Objects t1
  
  LEFT JOIN
      Segment t2
  ON
      t1.ObjectID=t2.ObjectID AND
      t1.ProjektID=t2.ProjektID AND
      t1.CraneID = t2.CraneID
  
  WHERE
      t1.ObjectID='esttest3' AND
      (x_ &gt; 0 OR y_ &gt; 0) AND
      x_ IS NOT NULL AND
      y_ IS NOT NULL
  GROUP BY
      t1.ObjectID
  </code></pre>
  <p>This takes about 1 minute for 3000 objects with 3 segments each.</p>
  <p>I've tried adding indices:</p>
  <pre><code>FROM Objects t1 WITH (INDEX(IX_Object_All))
  
  LEFT JOIN
      Segment t2 WITH (INDEX(IX_Segment_All))
  </code></pre>
  <p>But this did not improve performance.
  EDIT: These indexes were non-clustered, creating clustered indices fixed the issue. (No reference to indices was needed).</p>
  <p>If I change the LEFT JOIN into a INNER JOIN and this makes the extracted query(the one above) instantaneous(~0 sec). However inner join still takes a minute in my original query with extra fields (basically making it a left join again I suppose, since there will be values for each object)</p>
  <p>I also tried to create a view with calculated TotalLength and making a LEFT JOIN with the view. Still no improvement.</p>
  <p>My final idea is to create a trigger for the Segment table to update a TotalLength field in the Object table. I worry however that this will negatively impact performance when importing projects with ~9k segments, updating the value for each one...</p>