Prev: Functions in PUT statement
Next: Libname vs. Connect to
From: Patrick on 17 Oct 2007 09:38
Hi Dave
Only a partial answer. Others will hopefully give you more detailed
and accurate responses.
ODBC and OLE DB is middleware developed by Microsoft to access
databases. OLE DB is the newer standard.
The "connect to oracle.." in your code refers to the SAS/Access to
Oracle module.

Using a SAS client under Windows all three possibilities can be used
(if installed). Instead of writing a libname statement using the ODBC
engine you could also use the ORACLE engine (SAS/Access - see link):
http://support.sas.com/techsup/technote/ts703.html

To use the SAS/Access to Oracle module together with Passthrough SQL
will mostly perform best (using the libname statement with the Oracle
engine will often also generate pass through SQL - but can't always
find the most efficient way to do things).
Passthrough SQL means that you send your SQL within the connect block
to the Oracle DBMS and that the whole processing is done there. So try
to do it this way and to reduce the resulting data as much as possible
before you load it into SAS (everything outside the connect part)
because this datatransfer is the place where you loose the most time.
I've seen people reading 100MB of data out of a DBMS into SAS and then
using a simple selection which reduced this data to a few rows....

A session: That's you on your client having a connection to your SAS
Server. The session is up as long as you are connected to this server.
The session closes also when you exit your client.
I saw already zombies after a client aborted unexpected (with EG 2.1.
especially) - but it didn't happen too often and was more a problem
for the SAS Admin.

Hope this was of some help
Patrick

From: "Pardee, Roy" on 17 Oct 2007 17:53
The main difference is in which bits of the data processing happen
where--on the oracle server, or on the machine where you're running sas.
In general, you want to have oracle do as much processing as possible,
since it's closer to the actual data.

With passthru, you pretty much eliminate the possibility of SAS doing
much of anything--the contents of your SQL inside the parens get passed
to oracle pretty much unmolested. With libname access, it's much harder
to know what's going to get passed to oracle & what won't. Consider
this:

Data gnu ;
set ora.big_ass_table ;
my_date = datepart(oracle_datetime) ;
if my_date le '01jan2007'd ;
keep x1 x2 y3 my_date ;
Run ;

I'm pretty sure SAS would send oracle a simple "select * from
big_ass_table", hand the whole thing over to SAS (with all the I/O that
this entails) and then sas would chuck all the rows not meeting the IF
condition & all the fields not listed on the keep statement. Not
terribly efficient. So change it like so:

Data gnu ;
set ora.big_ass_table(keep = (x1 x2 y3 oracle_datetime)) ;
my_date = datepart(oracle_datetime) ;
if my_date le '01jan2007'd ;
drop oracle_datetime ;
Run ;

This is better--now instead of doing a "select *" on the oracle side,
sas will ask oracle only for the specific fields listed on the keep
dataset option. It's unfortunately not smart enough (I'm pretty sure
anyway) to try and translate that if statement into something it can
hand off to oracle as a WHERE. So you're still doing a bunch of
unnecessary I/O. So let's add a WHERE dataset option, like so:

Data gnu ;
set ora.big_ass_table(keep = (x1 x2 y3 oracle_datetime)
where = (datepart(oracle_datetime) le
'01jan2007'd)
) ;
my_date = datepart(oracle_datetime) ;
drop oracle_datetime ;
Run ;

You'd *think* that SAS would be able to cobble an oracle-intelligible
WHERE clause out of that to send to the db, but I don't think it does.
The reason is that there's a SAS function call in there, one for which
there isn't any equivalent native oracle function. Oracle has never
heard of datepart(), so it wouldn't know what to do with a WHERE clause
that included it. So here too you get all the rows & sas discards what
doesn't meet the condition. So here's how you can (I believe) get
oracle to do all of the work:

Data gnu ;
set ora.big_ass_table(keep = (x1 x2 y3 oracle_datetime)
where = (oracle_datetime <=
"01jan2007:00:00:00.000"dt)
) ;
my_date = datepart(oracle_datetime) ;
drop oracle_datetime ;
Run ;

BTW, one case where I'll pick libname access over passthru is when I
need to join a sas table to an oracle table. In that case, you can very
frequently speed things up considerably by using the DBKEY and
DBNULLKEYS dataset options.

As for oracle session length--I believe that SAS will keep your
connection open for as long as the libname is defined. To explicitly
close it you do a:

Libname clear ora ;

The analagous operation w/passthrough is DISCONNECT FROM.

Or just close down SAS. ;-)

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
David McNulty
Sent: Wednesday, October 17, 2007 3:17 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: SQL: Libname vs. Connect to

Hi Folks,

Could someone enlighten me on the difference between using a libname to
connect to an Oracle database and the pass through sql facility?

Example Libname

libname mylib odbc schema=name1 user=#### password="####" dsn="name2";
proc sql;
create table mytable as
select sas_select_clause
from mylib.table_name;
quit;

Example connect to

proc sql;
connect to oracle (path='pathname' user=#### password=####
preserve_comments);
create table mytable as
select * from connection to oracle
(select oracle_select_clause);
quit;


Whenever possible I prefer to use libnames as I do not need to worry
about the different flavours of SAS and Oracle SQL. For example in SAS I
can use the data set where option to qualify base tables and use the sql
where statement to manage the join. In Oracle SQL all the where
processing (I believe) is within the SQL where statement.

Recently the IT bods have raised issues over the efficiency of queries
against the company data base. My working assumption are:
1) The SAS libname odbc engine will convert my SAS into an efficient
query against the data base.
2) SAS manages all sessions (IT supplied word see question 1) against
the Oracle database and does not leave sessions hanging.

My questions are:

1) What is a session i.e. what SAS commands start and end a session?
2) Under what circumstances will SAS leave a session open but inactive.
3) Are the queries generated by the SAS/SQL libname interface efficient
(and how can I improve efficiency e.g. SAS features to avoid).

Regards

Dave

..
------------------------------------------------------------------------
----------------------------------------------

Bank of Scotland plc, Registered in Scotland Number SC327000 Registered
office: The Mound, Edinburgh EH1 1YZ. Authorised and regulated by
Financial Services Authority.
========================================================================
======
 | 
Pages: 1
Prev: Functions in PUT statement
Next: Libname vs. Connect to