LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (November 2000, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: rpresley <rpresley@GMCF.ORG>
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. -----------------------


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