views:

182

answers:

2

Is it possible to improve performance by taking the following SQL:

    SELECT t1.ID, t1.NAME, t2.SubName, t2.RefValue
    FROM Table1 as t1
    CROSS APPLY
    (
      SELECT Top 1 t2.SubId, t2.SubName, t3.RefValue
      FROM table2 as t2
      INNER JOIN table3 as t3
      ON t2.SubId = t3.SubId
      ORDER BY LastUpdated desc
    ) as t2

And rewriting it so that it looks like this:

SELECT t1.ID, t1.NAME, t2.SubName, t3.RefValue
FROM Table1 as t1
CROSS APPLY
(
  SELECT Top 1 t2.SubId, t2.SubName 
  FROM table2 as t2
  ORDER BY LastUpdated desc
) as t2
INNER JOIN table3 as t3
ON t2.SubId = t3.SubId
+1  A: 

Firstly, does it give the same result?

If so, what does the query plan say, and also set statistics io on?

gbn
A: 

How many rows in Table1, Table2 and Table3? How many intersect and end up in the result? I'm trying to figure out the purpose of rewriting the query, and agree with gbn... do you get the same result, does the query plan look the same in both cases, do the statistics i/o get any better, and does the rewritten query run any faster?

Aaron Bertrand
Yes I get the same results. Do you guys see something that suggests that these don't do the same thing. If it's any help subId maps to only one record in table3. The purpose of rewriting the query would be to improve performance. This is a simplified version of a much more complicated query i am rewriting. I'll let you know what the statistics/query plan say once I have it.
Abe Miessler