BookmarkSubscribeRSS Feed
santhosh
Fluorite | Level 6

I am fetching huge data from oracle based on ASOFDATE conditionbut it is taking more then 50 minutes

PROC SQL;

connect to oracle(user=XXXXXXX password="XXXXXXXX" path='XXXXXXXXXXXX');

CREATE TABLE ABC AS SELECT * FROM CONNECTION TO ORACLE

(SELECT * XYZ where ASOFDATE='15-AUG-2014');

DISCONNECT FROM ORACLE;

QUIT;

is there any way to minimise time taking


2 REPLIES 2
cpagrawal
Fluorite | Level 6

Hi Santosh

You can use ORACLE LIBNAME Engine to reduce the time.

Hope following will work for you - please set options correctly. ( path, schema, user , orapw )

LIBNAME oradbms ORACLE PATH=  SCHEMA=  USER=  ORAPW=   ACCESS = READONLY DBINDEX = YES ;

Best Regards

Chandra Prakash

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

I wouldn't expect there to be any difference in the operation of the libname syntax against the connect in the proc sql as the actual connection would be a tiny amount of time. 

I would suggest that you have a look at the data on the database and see if there are ways to cut down the amount of data to be output, firstly, do you really want all the variables to come out, if not remove the *.  How many records are you talking about with the where ASOFDATE='15-AUG-2014', restriction?  Maybe try parting it out into smaller subgroups.  It could also be other things, network speed, where files are being written to, amount of ram/processor etc.

Have you tried running the query in Toad or similar to see the operation there?  Also consider writing the data directly out to CSV from the database, then import to SAS, maybe quicker.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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