Using analytics with left join and partition by

Question

Hi guys,

I have two different queries which produce the same results. I wonder which one is more efficent. The second one, I am using one select clause less, but I am moving the where to the outter select. Which one is executed first? The left join or the where clause?

Using 3 "selects":

select * from
(
  select * from
  (
    select
      max(t.PRICE_DATETIME) over (partition by t.PRODUCT_ID) as LATEST_SNAPSHOT,
      t.*  
    from
      PRICE_TABLE t
  ) a
  where
    a.PRICE_DATETIME = a.LATEST_SNAPSHOT;
) r
left join
  PRODUCT_TABLE l on (r.PRODUCT_ID = l.PRODUCT_ID and r.PRICE_DATETIME = l.PRICE_DATETIME)

Using 2 selects:

select * from
(
  select
    max(t.PRICE_DATETIME) over (partition by t.PRODUCT_ID) as LATEST_SNAPSHOT,
    t.*
  from
    PRICE_TABLE t
) r
left join
  PRODUCT_TABLE l on (r.PRODUCT_ID = l.PRODUCT_ID and r.PRICE_DATETIME = l.PRICE_DATETIME)
where
  r.PRICE_DATETIME = r.LATEST_SNAPSHOT;

ps: I know, I know, "select star" is evil, but I'm writing it this way only here to make it smaller.

Answer 1

"I wonder which one is more efficent"

You can answer this question yourself pretty easily by turning on statistics.

set statistics io on
set statistics time on

-- query goes here

set statistics io off
set statistics time off

Do this for each of your two queries and compare the results. You'll get some useful output about how many reads SQL Server is doing, how many milliseconds each takes to complete, etc.

You can also see the execution plan SQL Server generates viewing the estimated execution plan (ctrl+L or right-click and choose that option) or by enabling "Display Actual Execution Plan" (ctrl+M) and running the queries. That could help answer the question about order of execution; I couldn't tell you off the top of my head.