Date:         Thu, 12 Dec 1996 09:48:37 PST
Reply-To:     TWB2%Rates%FAR@GO50.COMP.PGE.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         TWB2%Rates%FAR@GO50.COMP.PGE.COM
Subject:      Re: SAS/Access to Oracle Speed
Comments: To: jpearson@CPCUG.ORG

If the Oracle database is updated more frequently than you run your program,
then you will have to translate the data each time anyway and there is little
advantage to storing in SAS (between jobs).  If you will run your job many times
before the Oracle database changes, then retranslating each time will be very
expensive.
 
If the Oracle database is a legitimate relational database--one with many tables
and a variety of keys--do you want your SAS programmers to learn enough about
Oracle and the particular database to use it, or would you have a few
programmers translate between SAS and Oracle while the others work only in SAS?
Some queries are very easy to code badly, so that tremendous I/O is required to
retrieve a few records (if you join and select records in SAS rather than in
Oracle).
 
Have the folks who think using only one database is the true path mentioned that
using only one language is the true path?  If you dump SAS and learn Oracle
(SQL), you do not have to translate ( 8^) [tounge in cheek--Ed] ).
 
Hmmm, will the Oracle data reside where the SAS data resides now, or will the
data have to move across the network an extra time?  This would only happen if
right now you store SAS data and execute SAS on one machine, and the Oracle data
would be stored on a second machine.  There could also be network implications
if the SAS data resides on the sub-net on which it is used and the Oracle data
would reside on a different sub-net.  For example, any network traffic between
machines on the 10th floor here stays on the sub-net, while traffic between the
10th floor and the 11th floor has to compete for space on the building backbone,
as well as on two sub-nets.
 
I could not answer your actual question (Access<->Oracle efficiency) even if you
had specified your versions of both, your network and some idea of how complex
the data is, but you might get better answers from other people if you repost
with a few more details.
 
Good luck,
Tim Berryhill - Contract Programmer and General Wizard
TWB2@PGE.COM or http://www.aartwolf.com/twb.html
Frequently at Pacific Gas & Electric Co., San Francisco
The correlation coefficient between their views and
my postings is slightly less than 0
 
----------------------[Reply - Original Message]----------------------
 
Sent by:John & Andi Pearson <jpearson@CPCUG.ORG>
 
How efficient is the Access<->Oracle connection versus just importing
the file to SAS?  I assume it is slower, but how much slower?  Like 5%
or 50%?  We're debating a rule that requires all corporate data to be
kept in Oracle.  Is this rule going to be practical?