I have a connect to oracle Pass through Proc Sql that runs for 6 -8 hrs per load. When I try to rsubmit the same code to run parallel in 5 sessions, I was able to achive CPU time reduced to 50% but Real time has doubled.
Ultimate aim is to reduce real time but it has doubled and the CPU time has reuced.
I would have been happy if the real time is reduced and CPU time has gone up, but reverse results were got.
Any tips from you guys that I can implement to reduce Real time?
Without knowing the environment, data, code or objective, it is difficult to guess.
However, that said, my first guess would be that most of the time is IO based. Do you need to output all of the variables that you are currently keeping? If not, dropping unnecessary variables is an easy way to reduce real time.
It would be helpful to see the Proc SQL code you are using.
One tip for using the SQL pass-through efficiency is to eliminate any "SAS-isms" from the SQL code - meaning remove any SAS-specific code like dates, functions, formats etc. If Oracle sees any SQL code it doesn't recognize, it will dump all of the data to SAS and make SAS do the work - in effect you are then processing the data twice. So the idea is to make sure your Proc SQL code is compatible with Oracle's vision of SQL so that all subsetting, etc. gets done within Oracle. As a general fyi, each one of the SQL-based dbms (Oracle, MS SQL Server, etc.) use their own flavor of SQL and to achieve the maximum efficiency, you should be using only that type of code in your SAS Proc SQL pass through statements.
Another helpful tip is to set the "readbuff" option (in your connection statement) to increase the number of records transferred at a time between the DBMS and SAS. The default for Oracle is 250 but for SQL Server it is 1. You should set it to 1,000 for any batch job (any higher and you risk overwhelming SAS). This should help significantly with processing time.
I saw Paul Kent from SAS R&D talk about this very subject in 2007 - it was very helpful. We had one job that took a couple of hours to run that now runs in minutes - I'm not insinuating that you will get the same results but these two tips should help speed up your real time processing.