GilaMonster (4/16/2014)
The query is reading just over 14 million rows and aggregating that down to 1099. If it's reading 14 million rows from a table (the rows which satisfy the where clause), the reads you're seeing are pretty much expected (you're reading the majority of the table) and hence you may not be able to reduce it much more.
I have used row_number with CTE rather than max and group by, but it seems like query is not improving at all.
I am attaching execution for your information.
Query:
DECLARE @CURRENT smalldatetime
set @current = '6/30/2013';
with cte as (
select KeyInstn,DateEndedStandard , row_number() over(partition by keyinstn order by KEYINSTN desc) as row_num
from ObjectViews..InternalOperCBGEstimate
where dateendedstandard < @Current and mostrecentsequence = 1 and UpdOperation<2
)
select KeyInstn, dateendedstandard
from cte where row_num = 1