Date: Fri, 10 Nov 2000 10:40:00 -0500
Reply-To: HERMANS1 <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: HERMANS1 <HERMANS1@WESTAT.COM>
Subject: Re: 1. PROC SQL _method not working on remote submit 2. O
Content-Type: text/plain; charset=US-ASCII
The older "pass-thru" SQL method made it easier to understand the how the system
handles query improvement. It usually makes sense to use the Oracle server to
extract data before sending them across relatively slow networks, and the
"pass-thru" method specifies that. As a rule, only Oracle query improvement can
reduce significantly the processing time required for joins of Oracle tables.
Whether queries work faster in Oracle or SAS depends on the nature of the
queries, indexes and sort orders, and system configuration. Certified
specialists charge top dollar for "tuning" Oracle databases when performance
lags. Take a close look at the SQL programs before you tune your database. If
you can provide examples of the join queries, we can try to determine whether
they can be made more efficient. Sig
-----Original Message-----
From: rpresley <rpresley@GMCF.ORG> at Internet-E-Mail
Sent: Friday, November 10, 2000 9:23 AM
To: SAS-L@LISTSERV.UGA.EDU at Internet-E-Mail
Subject: 1. PROC SQL _method not working on remote submit 2. Oracl
Running SAS v8.1, remote submit to Unix box. I am trying to improve
performance on joining several large tables. The previously undocumented
feature _METHOD has returned useful information in the past (v6.12 or v8.1
running on local machine). It does not produce any additional information
in the log as submitted below.
proc sql _method buffersize=32000000 noprint;
Second question.
When Oracle tables are defined as SAS libraries we can submit code as though
the Oracle tables were SAS data sets (v8.1). Again performance is an issue.
If I submit a PROC SQL with the Oracle tables referenced as SAS data sets
where will the processing actually occur? Will the whole table be extracted
from Oracle one record at a time and then processed in SAS or will SAS pass
commands to Oracle (similar to the older SQL pass through) and Oracle join
the Oracle tables and return the results to the SAS environment.
Finally does anyone have actual data showing the performance of joining
Oracle tables with Oracle and then bringing the results into SAS versus
storing the data as SAS data sets (tables) and joining them in SAS. My data
sets (tables) range in size form 1/2 million records to 10 million records
with record lengths of 200 to 300 characters.
Thanks for your help and insight.
Rodney
Rodney J. Presley, PhD
Georgia Medical Care Foundation
57 Executive Park South, NE
suite 200
Atlanta, GA 30329-2224
404-982-0411 ext. 7574
404-982-7592 fax
rpresley@gmcf.org
------------------------
CONFIDENTIALITY NOTICE: This e-mail transmission, and any documents, files
or previous e-mail messages attached to it may contain proprietary,
privileged or confidential information. If you are not an intended
recipient, or a person responsible for delivering it to the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or use of any of the information contained in or
attached to this transmission is STRICTLY PROHIBITED. If you have
received this transmission in error, please immediately notify me by
reply e-mail and destroy the original transmission and its
attachments without saving them in any manner.
-----------------------
