Using Oracle DB as Workspace

[toshi]

Can I use oracle DB as a workspace to speed up output rather than using SAS Workspace as it takes more time and size while used in SAS EG? It often takes my worksspace upto 99 %. Is there any query or options in SAS to use ORACLE DB as workspace ?

[RW9]

I wouldn't have thought using a third party would be quicker, as you then incur the overheads of both sending data to the DB and retrieving from the DB.

If your process takes up a lot of resources (and you have checked for a good reason), then likely best bet is to speak with IT to get you some more resources.  

[Kurt_Bremser]

No, and for a very good reason. SAS writes not only datasets, but all kinds of temporary files to the WORK location. A DBMS could never handle that (it would only be able to deal with tabular data). Aside from that, you would incur (as @RW9 already stated) a massive overhead in network traffic to the DBMS server (unless the DBMS and SAS share the same physical and logical - same VM - server).

So you should either unload part of your SAS workload to the DBMS by using explicit SQL pass-through, or get more resources.

Or you find parts of your code that can be improved to be more efficient.

For that, you would have to post the code (along with some example data, if necessary - use a data step) that consumes so much of your resources.

[Patrick · Accepted solution]

@toshi

If you're interfacing with a database and dealing with large datavolumes then the most important thing in regards of performance is to plan/design your processes to limit data movements between the database and your SAS server.

In general: Reduce the data volumes which get moved between SAS and the database, i.e. by ensuring that joins between database tables fully execute in the database and when it comes to heterogenous joins between SAS and database tables that you code in a way that the smaller table gets loaded to the server where processing happens (which can be SAS or the database). 

And if you're using SAS EG and never disconnect from SAS for hours or even days then it also doesn't hurt if you implement some code which deletes SAS Work tables which you don't need any longer (done via Proc Delete or Proc Datasets/Delete).

[toshi]

Thank You all for your suggestions. I appreciate your valuable time.