Is there a performance difference between oracle via libname (sas/access) and using proc sql to do sql - pass-through ?

asked 23 Sep '11, 13:31

jcress's gravatar image

jcress
3518
accept rate: 0%


It depends on many factors, but I've found pass-through queries generally return results faster because they push the processing to the database and then return the results only, in contrast to the alternative in which many rows are are returned to be processed on the machine where SAS is running.

link

answered 27 Sep '11, 17:00

RichW's gravatar image

RichW
361
accept rate: 50%

Rich is correct to some degree. With each new version of SAS, however, they have been making the LIBNAME engine smarter. So, for relatively simple queries and for SAS functions that have direct mappings inside of Oracle, the LIBNAME engine actually constructs the Pass-through SQL statement for you (in appropriate Oracle dialect) and sends it to the DB, so theoretically the performance would be the same.

However, if you want to do something very "Oracle-like" and use Oracle SQL constructs that are not present in SAS, then you'll have to use Pass-through. For example, Index "hints" syntax can be used in pass-through that can't be used via LIBNAME.

So bottom line, if its simple SQL, then LIBNAME is the same as Pass-through, otherwise, use Pass-through SQL for best performance.

link

answered 05 Dec '11, 11:12

jay.l.stevens's gravatar image

jay.l.stevens ♦
1.0k624
accept rate: 36%

Not a precise answer to the quesiton, but if you do see a bit difference between passthrough performance and sas/access libnames then you can use

options sastrace=',,,d' sastraceloc=saslog nostsuffix; 
to find out exactly what SQL is being generated (and
options sastrace=none;
to turn it off again).

You may also see different results depending on whether you use the ODBC or ORACLE engines for your libnames.

link

answered 05 Dec '11, 11:31

WilliamDobson's gravatar image

WilliamDobson
567214
accept rate: 36%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×2
×2

Asked: 23 Sep '11, 13:31

Seen: 1,866 times

Last updated: 05 Dec '11, 11:31

powered by OSQA