BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

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?

Thanks,
Activa.
3 REPLIES 3
art297
Opal | Level 21
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.

Art
deleted_user
Not applicable
Well, its already coded to keep only the variables need and pulls only that it needs.

Process with rsubmit takes double the time than it runs without rsubmit. So why work on variables used?


I use UNIX and the sas version is 9.1. Objective is pull data from oracle to create a dataset.So a pass through SQL is used to process. Libname Oracle takes too much of time so Pass though is used.

Hope I give a brief idea of what I work on now.

Thanks
Activa
Ross
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 692 views
  • 0 likes
  • 3 in conversation