Code is running very slow, is my code efficent?

[Dlezotte]

I am running SAS EG at my new job and we pull data off of a server. The following code runs very slow, roughly 224 observations per second. I was researching and found that about 5,000 observations per second is considered normal. Below is the basic format of the code. Is the problem with the code or could it be just a server issue? I do know the master table we pull from the server is indexed, but I am not sure how it is indexed.

DATA new_table (RENAME =(var1=newvar1 var2=newvar2 .....));

SET database.Old_Table ( Keep = [77 var names]

WHERE = (date between xxxxxx and xxxxxx AND type = 'A' AND field < 7));

RUN;

NOTE: The data set WORK.New_TABLE has 73874 observations and 77 variables.
NOTE: DATA statement used (Total process time):
      real time           5:28.49
      cpu time            0.15 seconds

[Reeza]

You can try moving the RENAME to a PROC DATASETS which may be slightly faster, but there's nothing else I can see that would speed it up.

[mkeintz]

Notice the cpu time is almost zero, while the clock time is over 5 minutes.  So the bottleneck of your program is either

1. slow input or output process, which can mean
   1. You have a very slow input channel to/from the disk storage
   2. or you are competing for disk channel services with many other users or processes
   3. or (if you are using disk drives with moving disk heads) extreme disk fragmentation which can increase delays in writing your new dataset to disk
2. memory contention, due to
   1. The amount of memory needed by all simultaneous system users and processes exceeds the physically available memory.  In such a case, your program may have pages of memory being swapped to/from disk - as a means of "time-sharing" system memory.

Re run your program preceded by

  options fullstimer;

which will report resource usage such as disk data transfers, memory usage, and memory page swaps.  That may help diagnose your problem.

Also, you could also use a "DATA _NULL_;" statement (instead of "data new_table;") to see how much time consumption is due to disk data set writing.

[SASKiwi]

How big is old_table and new_table? How many rows are in old_table. Does specifying new_table (compress = yes) make any difference?

[LinusH]

You say "server", your libanme is called "database", and your CPU is close to zero.

Could it be that you are fetching data from an external RDBMS?

If so, add the following to our program:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Using that output, you can talk to your DBA to see if you together can optimize the code/storage.

If your data is in SAS, the other suggestions in the thread may apply. Consider creating an index. And to get better control on what is happening, add:

options fullstimer msglevel=i;