SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Viewing temp table taking more than 2 mins in SAS DI

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Viewing temp table taking more than 2 mins in SAS DI

Hi All,

 

I am facing problem to open the temp table created, after loading the data to extract transform.

I used join transform which gets data from 5 source tables, after that two extracts and then append and then extract trasform.

The last extract tranformation has 20 variables out of which 18 have case statements in expression builder.

When I ran the job, the data gets loaded in 2-3 secs (as i filtered data), but when I tried to open the data loaded in temp table next to extract, its taking more than 2 mins for 5 records.

When I opened the first 2 extracts temp tables they are opening in 2 secs. This opening of temp table is delaying the development work.

 

I havn't achanged any settings or options. I closed the application and tried again, but have same issue. It was working fine the day before this issue.

 

I appreciate if any one came across the same issue or have some solution near to that.

 

Thannks

Vicks


Accepted Solutions
Solution
a month ago
Respected Advisor
Posts: 4,665

Re: Viewing temp table taking more than 2 mins in SAS DI

@vickys

"It was working fine the day before this issue

Without any changes to the DIS job or the underlying data? If so then it looks very much "environmental" and it's very hard for us to know what this could be. 

 

Another thought: Are these "temp tables" SAS tables in Work or in a database? Are these actual physical tables or views? If views then be aware that the actual code execution only happens in the moment when you use the view (i.e. open it) which could explain the time you have to wait.

View solution in original post


All Replies
Solution
a month ago
Respected Advisor
Posts: 4,665

Re: Viewing temp table taking more than 2 mins in SAS DI

@vickys

"It was working fine the day before this issue

Without any changes to the DIS job or the underlying data? If so then it looks very much "environmental" and it's very hard for us to know what this could be. 

 

Another thought: Are these "temp tables" SAS tables in Work or in a database? Are these actual physical tables or views? If views then be aware that the actual code execution only happens in the moment when you use the view (i.e. open it) which could explain the time you have to wait.

Occasional Contributor
Posts: 10

Re: Viewing temp table taking more than 2 mins in SAS DI

Thanks, Patrick.

 

Yes, they are no changes in DIS Job or data.

 

These are views created in work. If I change to table from view do this effect my performance in loading and viewing the data.

 

Thanks

Super User
Posts: 9,855

Re: Viewing temp table taking more than 2 mins in SAS DI

If you have views into a database, opening them will be influenced by the state of the DBMS. If it's currently optimized for batch execution or undergoing internal restructuring, the performance of other queries may be significantly decreased.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 10

Re: Viewing temp table taking more than 2 mins in SAS DI

Thanks, Patrick.

 

I changed to table instead of view and its good, not taking more than 2 secs, while the job run is taking a while.

 

Respected Advisor
Posts: 4,665

Re: Viewing temp table taking more than 2 mins in SAS DI

@vickys

That's what I normally do during DIS development: Create the green tables as physical tables (not views). This not only allows me to easily step through the job and eyeball the data quickly (browse it), it also writes errors in the node where the issue is and not like with a view only in the node where I'm executing the view.

 

Once I'm happy with the DIS job I then select and test where to switch the physical tables to views to improve performance and reduce I/O and workspace usage. Using views can also impact negatively on performance so I'm implementing this normally based on a mixture of experience and "try & error".

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 270 views
  • 1 like
  • 3 in conversation