LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2007, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 11 Jul 2007 14:55:56 +1000
Reply-To:     David Birch <David.Birch@DISABILITY.QLD.GOV.AU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David Birch <David.Birch@DISABILITY.QLD.GOV.AU>
Subject:      Re: Libname to send back to Oracle taking long time
In-Reply-To:  A<CA8F89971ADA9F47A6C915BA2397844207B41D09@MAILBE2.westat.com>
Content-Type: text/plain; charset="us-ascii"

Jerry,

It's some time since I've used SAS/Access for Oracle, but back then I had similar issues loading data back into Oracle due to SAS defaulting to a Commit for every insert policy. Now, you can probably alter the policy quite easily. At the time I overcame the problem by using pass-thru SQL. However, the BulkLoad method is probably the best option.

HTH, Dave.

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Sigurd Hermansen Sent: Wednesday, 11 July 2007 3:20 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Libname to send back to Oracle taking long time

Jerry: The BulkLoad method makes the most sense. It bypasses integrity constraints for each tuple insert in favor of writing integrity constraint violations to a file.

As an experiment, I'd try an equivalent SQL construct:

select * from (select * from ora_air.ORACLE_TABLE) union all (select * from PRD_DATA.sas_dataset) ;

Oracle doesn't do UNION's efficiently, so SAS/Access for Oracle may substitute an equivalent of BulkLoad.

Here's what you have to do to give the UNION the best chance of working: 1. create sas_dataset with the exact structure and integrity constraints as ORACLE_TABLE; 2. assign date formats; 3. extract primary key values from ORACLE_TABLE and intersect with equivalent primary key variables in sas_dataset to identify, then exclude, key integrity violations.

If ORACLE_TABLE has indexes defined, you may need to redo the indexes. S

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Jerry Sent: Tuesday, July 10, 2007 12:56 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Libname to send back to Oracle taking long time

Hi,

So I am loading data back into oracle using the libname facilities for 4.5 million records and its taking about 10 hours consistently.

I am using the following:

libname ora_air Oracle user=uid password=xxxx path=OracleDB1; libname PRD_DATA "\\server\directory\";

proc sql ; insert into ora_air.ORACLE_TABLE( char1, date1, date2, Num1, sasdatefmt=(date1='datetime20.',date2='datetime20.')) select char1, date1, date2, Num1 from PRD_DATA.sas_dataset; quit;

char1 is a character with length of 39 i have 2 dates values and a number values being sent.

There are 4.5 million records and it is taking me 10 hours!!!

SAS support suggested using BulkLoad, but for some reason our server and SAS Admins is having trouble getting that to work. Is there any other suggestions out there?

********************************* DISCLAIMER ********************************* The information contained in the above e-mail message or messages (which includes any attachments) is confidential and may be legally privileged. It is intended only for the use of the person or entity to which it is addressed. If you are not the addressee any form of disclosure, copying, modification, distribution or any action taken or omitted in reliance on the information is unauthorised. Opinions contained in the message(s) do not necessarily reflect the opinions of the Queensland Government and its authorities. If you received this communication in error, please notify the sender immediately and delete it from your computer system network.


Back to: Top of message | Previous page | Main SAS-L page