Skip to main content
Solved

Temporal table "for system time" makes task very slow

  • February 28, 2021
  • 3 replies
  • 697 views

Forum|alt.badge.img+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.

Best answer by Roland

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

3 replies

Forum|alt.badge.img+17
  • Moderator
  • 761 replies
  • March 1, 2021

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/

 


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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


Forum|alt.badge.img+13
  • Author
  • Hero
  • 149 replies
  • Answer
  • June 3, 2021

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.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings