SAS Program execution speed slows after 10,000 iteration

[ShammiKalsi]

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

[Ksharp]

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]

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]

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]

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]

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]

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

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