topic Re: Performance Issue in Proc append in SAS Studio

Performance Issue in Proc append

Helannivas — Thu, 09 Nov 2017 11:21:21 GMT

Hi,

We are using DI studio to load the data from Source to Netezza database. In our code , we are using Proc append to load the data from the work directory and into the table.

For Most of the days, the append is consuming less amount of time for 33 crores record (approx 8 mins) , but specifically on Monday, the proc append step consuming around 01 hr 15 mins. Im not sure about the reason for this high difference.

Could anybody please confirm the potential reason for this huge amount of consuming time. Thanks in advance.

Re: Performance Issue in Proc append

RW9 — Thu, 09 Nov 2017 11:35:45 GMT

Erm, with no information I can only guess that there is something else happening on Monday? Why not speak with your IT/Support group for the software and get them to monitor activity over a week and see what other processes run on a Monday. Maybe 32 cores have a day off on Monday, its really impossible to say without knowledge of the system - I mean do you know why my program runs slowly on Thursday?

Re: Performance Issue in Proc append

ChrisBrooks — Fri, 10 Nov 2017 09:50:00 GMT

Agree with @RW9 but it's also possible that if your program only runs 5 days a week (Mon-Fri) but data comes in on 7 days a week then on Monday you may be appending a lot more records than any other day. I once had the opposite situation i.e. the weekend runs of a suite of programs ran like greased lightning because there were rarely any records to take on as opposed to weekday runs.

Re: Performance Issue in Proc append

LinusH — Mon, 13 Nov 2017 09:34:32 GMT

Step one, add more logging:
Options fullstimer msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Reasons can be more data, I/O OR network contention or other resource constraints in Netezza. Work together with the DBA to understand/resolve.