Date:         Mon, 23 May 2011 12:03:20 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: General considerations for efficient downloading data from
              Oracle via SAS/access to Oracle?
Comments: To: Mary H <mlhoward@AVALON.NET>

Thanks Mary!

I was wondering if I can have my own "WORK" space, so that I can
create temporary datasets/tables in the remote Oracle, then work on
them to get aggregate data to avoid transferring huge data over the network.

I guess what you recommended "myschema" is something like it. But how
do I create a schema? If the schema I'm using is a "data view", does it mean
I am not permitted to create a schema?

Ya



On Mon, 23 May 2011 07:49:37 -0700, Mary <mlhoward@AVALON.NET> wrote:

>Sometimes it is that when other users have access to the same table on
Oracle, your passthough is getting low priority.  If you have rights to
write to your own schema in Oracle, sometimes making a copy of the data you
want to pull to your own schema will help.   The Oracle code would be like
this:
>
>create table myschema.table1 as
>select var,var from bigschema.table1;
>
>Then try to pull from myschema.table1, since no one else would be using
that.
>
>-Mary
>
>--- ya.huang@AMYLIN.COM wrote:
>
>From: Ya Huang <ya.huang@AMYLIN.COM>
>To: SAS-L@LISTSERV.UGA.EDU
>Subject:      Re: General considerations for efficient downloading data
from              Oracle via SAS/access to Oracle?
>Date:         Fri, 20 May 2011 14:00:25 -0400
>
>Yes, it is pulling data over a network. I would say oracle is 3000+ miles
>away from my PC. The final dataset is not very big (2 GB), and data only
has
>10 columns. I've tried to remove the where claus so there is nothing to
>be optimized, a straigforward downloading, it ended up taking more than one
>hour.
>
>What's weird to me is that another dataset (0.5 GB), only took about
>2 minutes to download.
>
>Any possibility that different type of "table" in oracle will result in
>totally different efficiency, in terms of downloading speed?
>
>
>
>On Fri, 20 May 2011 13:34:37 -0400, Durrett, Cindy
><CindyDurrett@FDLE.STATE.FL.US> wrote:
>
>>Are you pulling it over a network? How many records are in myorac.a?  How
>many in myorac.b?
>>
>>We pull data similarly to how you've described across a network which
>sometimes slows things down. Also our larger datasets take longer than the
>smaller ones (larger = over 20 million records).
>>
>>
>>-----Original Message-----
>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya
>Huang
>>Sent: Friday, May 20, 2011 1:29 PM
>>To: SAS-L@LISTSERV.UGA.EDU
>>Subject: General considerations for efficient downloading data from Oracle
>via SAS/access to Oracle?
>>
>>Hi there,
>>
>>I need to "download" data from an Oracle database via SAS/Access for
>Oracle.
>>I'm frustrated by the speed of downloading. I've tried to limited
>>the variable to be downloaded. For most of the data, time to download is
>>acceptable.
>>But a few of the dataset just take way too long (30 minutes +), even
though
>>I only selected 10 variables.
>>
>>Wonder what's the general consideration to deal with this kind of
>situation?
>>
>>Thanks
>>
>>Ya
>>
>>The code is basically like below:
>>
>>libname myorac oracle path=&URL user=&UID pass=&PSS schema=&schema;
>>
>>data x;
>> set myorac.a (keep=v1 v2 v3 .. v10);
>>run;
>>
>>proc sql;
>>create table y as
>>select v1,v2,v3
>>from myorac.b
>>where id in (select id from x)
>>;