0

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 ?

codingIsCool
  • 64
  • 1
  • 9
  • I think you want to `PARTITION BY mt.one_id` or perhaps `PARTITION BY mt.one_id, mt.one_date, mt.another_date` if `mt.one_id` is not distinct. That will give you a sequence of row numbers representing sub-table rows that resets for each row of your main table. Filtering on row-number = 1 should then yield your desired results. – T N Jun 02 '23 at 14:59
  • Creating an index on `sub_table(another_id, one_date, another_date, yet_another_date)` or `sub_table(another_id) INCLUDE (one_date, another_date, yet_another_date)` may also help. That would allow the query to select, sort, and filter the sub-table rows using only the index, deferring the main table (clustered index) lookup until after the "top 1" or "row-number = 1" filter is applied. I find this particularly useful when the table is what I call a "wide table" with lots of other data. The index can scanned with much fewer reads than the table. – T N Jun 02 '23 at 15:10
  • @TN - sub_table has an index as suggested already. Its mentioned in my question. Thanks. – codingIsCool Jun 02 '23 at 15:14
  • An index isn't magically going to make the query fast if the index doesn't cover. Do you need `*` from both tables? Why don't you try just putting the index columns in the select list inside the apply instead of `*`? – Stuck at 1337 Jun 02 '23 at 15:20
  • @codingIsCool - The index I suggested also includes `yet_another_date`, whose presence could be enough to avoid a table scan on sub_table. Without it, the query engine must look up all `yet_another_date` values in sub_table before it can select the "top 1", even if all of the values are the same. Having `yet_another_date` in the index should save a lot of reads. It might even make the original `CROSS APPLY` version of your query sufficiently efficient to negate the need for a rewrite. – T N Jun 02 '23 at 15:30
  • @Stuckat1337 - The actual query does not have *, I just used it for the question. Its has 250 columns picked from 25 tables. 100+ columns come from the sub_table. – codingIsCool Jun 02 '23 at 15:33
  • And are all 100 columns in the index? Whether it's `*` or `way too many unindexed columns` it's the same thing. – Stuck at 1337 Jun 02 '23 at 15:38
  • I don't think "\*" (or select 100 columns) would be an issue, since the extra data need not be retrieved until all of the filters are applied. Ideally the bulk of the row selection and filtering should be done at the index level, so that the final data selection is performed using a series of clustered index seeks that only accesses the rows needed in the final result. The index(es) should include just enough to support efficient selection, sorting, and filtering for the expected use cases, while avoiding *index bloat* that results from including too many columns. ... – T N Jun 02 '23 at 15:50
  • ...The 250 columns in sub_table would definitely qualify as a *wide table* in my opinion, so having a lean index that allows most of the work to be done outside of that table is the key to efficient execution. – T N Jun 02 '23 at 15:50
  • @TN But, sometimes, SQL Server can evaluate the select list first, or materialize all the data first, or try to evaluate expressions on columns that would later be filtered out anyway. There is no guarantee that logical processing order matches actual runtime processing order. It's why `SELECT 1/foo FROM ... WHERE foo > 0` can sometimes fail with a divide by 0 error. – Stuck at 1337 Jun 02 '23 at 16:30
  • So, I've found it can be beneficial to force SQL Server to evaluate _just the columns that are in the index you expect to be most useful_ and then pull in the other columns you need after you know filtered rows have been filtered out. The optimizer is good but there are still plenty of pits to stumble into. – Stuck at 1337 Jun 02 '23 at 16:31
  • _basically its not doing any ordering, for the data that is there now_ - I'd rather have a look at the query plan. There actually might be a costly `sort` operator, not an expected `top`. If `another_id, one_date, another_date` is the actual order of fields in the index, than the result before `TOP` would be ordered by `one_date`, not `another_date`. If there is no variance in `another_date`, why don't just drop the `order` clause? Or switch the ordering to `one_date`? – Dmitry Jun 02 '23 at 19:45

0 Answers0