score:0

I know the profiler should be able to handle all these cases, but I'm going to ask anyway...

Have you tried profiling any of the following:

  • Converting the inner selection to a CTE?
  WITH CTE_Versions AS (
      SELECT DataID, MAX(VersionNum) as mVersion 
      FROM t1
      GROUP BY DataID 
  )
  SELECT DISTINCT t1.status AS Status
  FROM t1
  JOIN CTE_Versions AS b
      ON t1.DataID = b.DataID 
      AND t1.VersionNum = b.mVersion
  JOIN table2
      ON t1.table2ID = table2.ID
  WHERE table2.field = @parameter
  • Adding locking hints?
  SELECT DISTINCT t1.status AS Status
  FROM t1 with (nolock)
  JOIN (
          SELECT DataID, MAX(VersionNum) as mVersion 
          FROM t1 with (nolock)
          GROUP BY DataID 
  ) AS b
      ON t1.DataID = b.DataID 
      AND t1.VersionNum = b.mVersion
  JOIN table2 with (nolock)
      ON t1.table2ID = table2.ID
  WHERE table2.field = @parameter
  • Moving the parameter into the join? (as per @X-Zero's comment)
  SELECT DISTINCT t1.status AS Status
  FROM t1
  JOIN (
          SELECT DataID, MAX(VersionNum) as mVersion 
          FROM t1
          GROUP BY DataID 
  ) AS b
      ON  t1.DataID = b.DataID 
      AND t1.VersionNum = b.mVersion
  JOIN table2
      ON  t1.table2ID = table2.ID
      AND table2.field = @parameter

I'd be interested to hear how those three (or a combination thereof) would go?


More questions

More questions with similar tag