BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

9 Comments »

  • Thanks SASKiwi …. good information!

  • SASKiwi says:

    Actually for ODBC Pass-through at least, READBUFF is a supported option. Check the SAS documentation. I use it all the time and tend to just set the maximum allowed value of 32767. This appears to work well with most tables that do not have large numbers of columns.

  • Sounds cool .. any more details?
    Is it open source?

    What kind of speed have you seen?

  • Hello says:

    Pass Through queries do not use a libname as it submits the code directly to the Database in question… so you are not using a libname statment as that is a SAS thing!!! There however may be connections strings items that you may be able to pass in your connect string to improve the performance of data pull.

  • Hello says:

    You should take a look at Hyperstage to see what a real fast database can do. Something to look at if you want real fast load and retrieval!!!

  • Steve Overton says:

    Agreed, the SAS administrator should definitely manage these settings through the library definition in metadata if you do not know how to best configure these settings. This tip comes from my experience doing development work on my own server. In the real world this will be very different!

  • Chris S. says:

    What I never got was how you were supposed to figure out how to set these parameters. I suppose you could complete a bunch of tests and vary the numbers systematically, in the end selecting the best response time for say 100,000 records. Seems a bit complicated for on analyst to complete, and it should really be done by an administrator and set or suggested to all users.

  • Steve Overton says:

    These options will not work for pass-through queries since SAS passes the SQL code directly to the third-party database, Oracle in your case. Also, these commands tell SAS to buffer data before sending to the target database. Passing through code bypasses this altogether.

  • Quentin says:

    Nice post, and comes at a good time for me as I just started working on a DI studio job that will do big nightly pulls from an Oracle database.

    I’m using a pass-through query. Assume these options work for pass-through queries as well?

    Thanks!
    –Quentin