I have the following scenario (I have removed most of the fields to simplify my question):
Stage_tbl
date1 datetime
date2 datetime

Fact_tbl
date1_time_key int
date2_time_key int

Dim_Time_tbl
time_key int
start_date datetime
end_date datetime

There are no indexes on any of the fields except time_key (auto incr int pk)

update Fact_tbl
set date1_time_key = (
select time_key from Dim_Time_Tbl
where date1 > start_date
and date1 <= end_date)
3 seconds

update Fact_tbl
set date2_time_key = (
select time_key from Dim_Time_Tbl
where date2 > start_date
and date2 <= end_date)
2 minutes 42 seconds

The execution plans are the same. What could be the reason for the execution time difference? The uniqueness of date1 is 247

distinct records on a total of 50249 and date2 is 19951 distinct records. The execution plan shows a full scan on the

Stage_tbl which accounts for 64%. I added an index to date2 and the cost for the Stage_tbl read went down to 17% but the

total execution plan remained at 2 minutes 42 seconds.