I have a reasonably large ETL table (170M rows, 40GB).
The clustered index is the EVENT_DTTM.
Every day a new batch of data is inserted and processed. Part of the processing is updating each row (only the newly inserted ones), based on other rows in the table. (e.g. compare the datetime of each row to the row before it and calculate "event time"; detect row with malformed data and use data from nearby rows to correct it.)
I'm using an OUTER APPLY (top 1) structure to do this. Basically, something of the form:
select *, event_duration = datediff(ss, alias.EVENT_DTTM, TABLE.EVENT_DTTM) from TABLE outer apply (select top 1 EVENT_DTTM from TABLE alias where alias.EVENT_DTTM >= @min_date and alias.EVENT_DTTM < @max_date and alias.ETC = TABLE.ETC) alias where TABLE.EVENT_DTTM >= @min_date and TABLE.EVENT_DTTM < @max_date
My problem is that SQL appears to be scanning the entire table for the OUTER APPLY, instead of just the range between @min_date and @max_date.
Here is an estimated query plan for the same query but with different parameters. The query does not run in parallel but the index spool is present.
Does anyone have an idea why this might be, and how to fix it?
I've tried building a CTE limited to the given date range, and then updating that. I've tried building a dynamic SQL string using literals for the dates. Neither of these has worked.
The only thing that I've found that works is to build a filtered index on the date range. SQL will then use the filtered index for the OUTER APPLY. This isn't very elegant and writing out the index costs a lot of time.
Unfortunately, I don't have time to implement and kind of partitioning scheme at this time.
For the sake of completeness, here is an example of my actual code:
update fact_cabinet_event_base set assoc_event_device_id = assoc_event.device_id, assoc_event_num = assoc_event.event_num from fact_cabinet_event_base outer apply (select top 1 device_id, event_num from fact_cabinet_event_base assoc_event where assoc_event.event_datetime >= dateadd(dd, -3, @populate_min_date) and assoc_event.event_datetime < @populate_max_date and assoc_event.event_type = 'dispense' and assoc_event.facility_applix_number = fact_cabinet_event_base.facility_applix_number and assoc_event.idr = fact_cabinet_event_base.idr and assoc_event.idri = fact_cabinet_event_base.idri and assoc_event.idria = fact_cabinet_event_base.idria and assoc_event.event_datetime < fact_cabinet_event_base.event_datetime order by assoc_event.event_datetime desc) assoc_event where fact_cabinet_event_base.event_datetime >= @populate_min_date and fact_cabinet_event_base.event_datetime < @populate_max_date and (fact_cabinet_event_base.event_type = 'waste' or (fact_cabinet_event_base.event_type = 'return' and fact_cabinet_event_base.event_subtype <> 'return to pharmacy')) and (fact_cabinet_event_base.assoc_event_num is NULL or fact_cabinet_event_base.assoc_event_num = 0)