Reputation: 72
I've a query that does the below
select * from main_table mt
OUTER APPLY
(
select top 1 * from sub_table st
where mt.one_id = st.another_id
and st.one_date > mt.another_date
and st.another_date < mt.one_date
ORDER BY st.yet_another_date DESC
)
There is a clustered index on st with columns st.another_id, st.one_date, st.another_date. And yet_another_date is the same across all records for one another_id - so basically its not doing any ordering, for the data that is there now.
Both tables contain 100 million. And there are 20 other tables in the query, and even other outer applys. The whole query takes a day to complete.
In an attempt to optimize the same, I've created a temp table off sub_table inner joined with main table with the 3 conditions. And a ROW_NUMBER
column that is intended to give me the TOP 1
for each combination as shown below
ROW_NUMBER() OVER (PARTITION BY st.another_id, st.one_date, st.another_date ORDER BY st.yet_another_date DESC)
And then doing LEFT JOIN
in the main query to this temp table with the same 3 conditions.
This is not giving me the right data for the columns from st that are in the main query. There is significant improvement in the time to complete. If only the results were correct...
Anyone know how the ROW_NUMBER
OR LEFT JOIN
should be corrected to fix the issue? Or another way to optimize the OUTER APPLY
?
Upvotes: 0
Views: 157