Skip to main content
To homepage To homepage
Solved

Slow performance ODX transfer

D
Contributor

Hi, 

I'd love to get your thoughts on issue we're dealing in the past few days. We've noticed that transfer time from ODX to DSA is growing rapidly. This as significant impact on total running time of DWH and SLA towards our client

Execution log of DSA 

As you can see. as insert and cleansing tasks remains pretty much the same, transfer time is growing.

In terms of volume, data hasn't grown significantly  

 

Batch Id 13428 (first) - 46347863 rows

Batch Id 13481 (last)  46536187 rows (+0.4%)

difference in transfer time is more apparent as you zoom in into the package execution (Gantt chart)

Batch Id 13428 

 vs last batch (13481)

There is no inconsistencies in ODX que or ODX service logs found. 

I've also checked it there is long running transactions (sp_who2) in both TX and DWH server but could find anything out of the ordinary   

Our setup consists of ODX repository and DWH storage on same Azure Managed Instance. TX application server is (still) on-prem 

My burning question is: what can attribute the long running transfer tasks and what can we do to solve it? 

With kind regards, 

Dror

Best answer by Syed Yousuf

Your current version includes auto-cleanup of logs, to deletes logs older than 90 days.  However, based on the number of data sources and the frequency of task executions, it may still have a lot of logs.  You may try to delete logs manually. Also review Troubleshooting concurrency issues in ODX data sources 

View original
Did this topic help you find an answer to your question?

5 replies

S
Contributor
Forum|alt.badge.img+3
  • Syed Yousuf
  • Contributor
  • 82 replies
  • August 26, 2022

1. Which version of ODX are you using?

2. Are there other ODX tasks or executions running?

3. Review the backlog size

Use this guide to connect to the backlog (copy) using SQLiteStudio

How to Query ODX local Backlog SQLite database

Run the following SELECT queries (1 statement at a time)

SELECT COUNT(*) FROM [ExecutionLogs]
SELECT COUNT(*) FROM [ExecutionTasks]
SELECT COUNT(*) FROM [ServiceLogs]

D
Contributor
  • dsvartzman
  • Author
  • Contributor
  • 19 replies
  • August 26, 2022

Hi Syed, 

Thanks for looking at it. 

1. ODX version: 20.10.26

2. DWH execution is typically running after (sequentially) ODX tasks. so no, there shouldn't be overlap. there is also no standing ODX task on ODX que

3.

SELECT COUNT(*) FROM [ExecutionLogs] > 1670
SELECT COUNT(*) FROM [ExecutionTasks] > 41355
SELECT COUNT(*) FROM [ServiceLogs] > 150

 

Backlog size is 1.1MB

 

 

S
Contributor
Forum|alt.badge.img+3
  • Syed Yousuf
  • Contributor
  • 82 replies
  • August 26, 2022

Hi Dror,

Can you double check backlog size ( 1.1 MB or 1.1 GB ?)

Can you test your scenario in the latest version?  There are a few performance improvements. 

Please review Release Notes for TimeXtender 20.10.x

Also review Troubleshooting concurrency issues in ODX data sources 

D
Contributor
  • dsvartzman
  • Author
  • Contributor
  • 19 replies
  • August 28, 2022

Hi Syed, 

I stand corrected. Database is not lite (light) anymore. 1.1 GB

Are there are Cleanup scripts we can use? 

I will plan an emergency upgrade as this issue is getting worse by the day    

With kind regards, 

Dror 

 

S
Contributor
Forum|alt.badge.img+3
  • Syed Yousuf
  • Contributor
  • 82 replies
  • Answer
  • August 30, 2022

Your current version includes auto-cleanup of logs, to deletes logs older than 90 days.  However, based on the number of data sources and the frequency of task executions, it may still have a lot of logs.  You may try to delete logs manually. Also review Troubleshooting concurrency issues in ODX data sources 

Reply


Most helpful members this week

Terms and ConditionsCookie settings

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