BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vickys
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

5 REPLIES 5
Patrick
Opal | Level 21

@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.

vickys
Obsidian | Level 7

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

Kurt_Bremser
Super User

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.

vickys
Obsidian | Level 7

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.

 

Patrick
Opal | Level 21

@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".

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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