Solved

Temporal table "for system time" makes task very slow

  • 28 February 2021
  • 3 replies
  • 511 views

Userlevel 4
Badge +13

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.

icon

Best answer by Roland 3 June 2021, 14:35

View original

3 replies

Userlevel 6
Badge +16

Can you get both execution plans from the cache? And post them here? Often when something is fast in SSMS but not in the application you are dealing with 2 different execution plans. 

If you don't know how to get the execution plan from cache see this article:

https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/

 

Userlevel 7
Badge +23

@Roland, have you tried what my colleague has suggested?

Userlevel 4
Badge +13

A very belated answer: :blush:

Upgrading the suite (to version 2021.1 and now 2021.2) fixed the issue. This code is now just as fast in our end product as it is in SSMS.

Reply