For a certain task it’s required to query a system-versioned aka temporal table at a certain point in the past. Let’s call this table temporal_table for simplicity.

Here’s an example query:

select v.*
from   temporal_table for system_time as of '2021-2-27 14:59:46' v

In SQL Server Management Studio (SSMS) this runs in about 2 seconds for this particular table. Using this in a lengthy select statement with a bunch of joins also runs rather fast in SSMS as it takes about 10 seconds to finish.

The problem happens when this code is used in a task in our end product. As soon as the for system_time flag is used in the SQL code the task goes from running 10 seconds to what’s basically forever. This is the same code used in SSMS, with the exact same datetime for testing purposes. (This date will be replaced by a parameter in a later stage as it needs to be dynamic.)

I’ve tried to get around the issue with this alternative query:

select v.*
from   temporal_table for system_time all v
where  '2021-2-27 14:59:46' >= v.tsf_valid_from
and    '2021-2-27 14:59:46' <  v.tsf_valid_to

It runs just as quick in SSMS as the previous query but also grinds the task in our end product to a halt.

Suspecting it might be the for system_time flag that trips up the task I also attempted this:

select t.*
from ( select *, tsf_valid_from, tsf_valid_to
       from   temporal_table

       union all

       select *
       from   temporal_table_history
     ) t
where '2021-2-27 14:59:46' >= t.tsf_valid_from
and   '2021-2-27 14:59:46' <  t.tsf_valid_to

Unfortunately this doesn’t change anything. The query remains just as fast in SSMS but still doesn’t fix the task in our end product.

I’ve also tried to first capture the output of these simply queries in a table variable and use that instead in the rest of the code but that too doesn’t get around the issue. My thinking was this could be caused by the joins taking place but apparently that’s not the case.

What’s happening here? Am I overlooking something? We’re currently on version 2020.2.12.