BookmarkSubscribeRSS Feed
ShammiKalsi
Fluorite | Level 6

Hi Team,

i have a sas program which generates the pdf password protected file and send across the users.

users are around 2lakh. when i run sas program around 10000 files generates fast but after 10000 sas progam execution speed degrade upto 60 %.

 

program flow.

1. Read the data from oracle db by using ODBC.

2. perform sorting on data.

3. create macro on email and call value during run time in loop.

4. grenerates pdf, save it temp location and send to user.

4. clear tmp files.

5. stop.

 

Pls help how to increse execution speed..

7 REPLIES 7
Ksharp
Super User
Did your clear LIBNAME of odbc after running every sas code ?
libname xxxx clear;

Or Did you disconnect from oralce/odbc ?
proc sql;
connect to .....
......
disconnect from  ......
quit;
ShammiKalsi
Fluorite | Level 6
Hi

I do read data from oracle by using ODBC and store it into sas work lib.
once program executed i closed the session..
s_lassen
Meteorite | Level 14

Sounds like you generate an awful lot of data, if you generate a PDF file for each of thousands of users. Perhaps you are running short on disk space, or memory, or both. From your description it sounds like you generate all the temporary files and send them, and when all that is done, you delete the files. You could try deleting each file after sending, that may help.

 

But it is not obvious that the problem is with your machine. Another possible source of delay may be the email server. Such a thing probably has limited bandwidth, and for starters it will (I guess) store the mails in a buffer for sending. When that buffer runs full, your program slows down, because it has to wait for one file to be sent before it can give another to the server.

 

So you may have to monitor performance (memory, disk space for temporary files) on your own server to see if there is an identifiable bottleneck. If nothing shows up, you will probably have to talk to the guys in charge of the Email server, to see if there is something they can do (e.g. allocate more disk space for the mail output buffer), and perhaps have them take a look at performance on the email server while your program is running.

ShammiKalsi
Fluorite | Level 6
Thanks for your response..

I have checked there is no issue at memory, disk space and temporary file
size. i do delete temporary file immediate after sending the mail.

As you suggested problem could be at smtp server side. Let me explain one
more scenarios here..

I have run two batches 50,000 each. I notice that for both batches when i
executes with different session it executes fast for 10,000 records. but
after that both batches gets slow down..
Tom
Super User Tom
Super User

Are you running on a server?  Perhaps your server has a policy of downgrading your priority when you have been running for too long?  So it looks like you are running slower because you are being given a smaller percentage of the system resources.

SASKiwi
PROC Star

@ShammiKalsi  - I suggest you check the SAS log of the first one or two slow iterations. What code steps are slow compared to your fast ones? All of them, one of them, several of them? Compare the elapsed time with the CPU time on each step. If it is only one step that is causing the slowness then what is that step doing? Looking at your SAS log in this way will give you clues to what is causing the slowness.

ChrisNZ
Tourmaline | Level 20

> I have run two batches 50,000 each. 
Not a solution, but rather a workaround: How about 10 batches of 10,000?

Also avoid using lackh or crore, few people know what this means.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 824 views
  • 1 like
  • 6 in conversation