Plan, execute, measure and optimize customer experiences

SQL Pass-through

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

SQL Pass-through

Hi All

Is there a setting in CI Studio that will cause implicit pass-through in selection queries where it is possible?


Accepted Solutions
Solution
‎06-22-2016 09:33 AM
SAS Employee
Posts: 76

Re: SQL Pass-through

If you're using calculated items defined in your information map or in CI Studio, the tips from @LinusH are invaluable for setting up your environment (SQL_FUNCTIONS=ALL, making sure all your libnames have the exact same settings except SCHEMA=, etc.). And the option statement below, combined with capturing the query from your CI core log (on the mid tier) and running it in EG with a proc sql statement ("proc sql loops=1000 _method feedback;") will let you see if that calculated item is being passed or not:

 

ACCESS ENGINE:  SQL statement was not passed to the DBMS, SAS will do the processing

 

If SAS has to do the processing, it will download all the data, then do the calculation on the compute server.

 

If, on the other hand, your question is more about the queries that get generated from the information map for non-calculated fields, there is no switch to control that; it's always generated sql that's executed using the normal SAS/ACCESS & proc sql method

View solution in original post


All Replies
Super User
Posts: 5,317

Re: SQL Pass-through

I'm not familiar with CI Studio, but if you are using SAS/ACCESS libnames, implicit pass-through will occur by default. There are some settings that affect (mostly reduce) what's gets "pass-throughed".

 

See the specifics for you database. Here's the doc for Oracle:

Functions: https://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p0f64yzzxbsg8un1u...

Joinshttps://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p0idw091gkbf6hn17...

 

Also, to monitor pass through behaviour, add this to your SAS program/autoexec...:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

 

Data never sleeps
Solution
‎06-22-2016 09:33 AM
SAS Employee
Posts: 76

Re: SQL Pass-through

If you're using calculated items defined in your information map or in CI Studio, the tips from @LinusH are invaluable for setting up your environment (SQL_FUNCTIONS=ALL, making sure all your libnames have the exact same settings except SCHEMA=, etc.). And the option statement below, combined with capturing the query from your CI core log (on the mid tier) and running it in EG with a proc sql statement ("proc sql loops=1000 _method feedback;") will let you see if that calculated item is being passed or not:

 

ACCESS ENGINE:  SQL statement was not passed to the DBMS, SAS will do the processing

 

If SAS has to do the processing, it will download all the data, then do the calculation on the compute server.

 

If, on the other hand, your question is more about the queries that get generated from the information map for non-calculated fields, there is no switch to control that; it's always generated sql that's executed using the normal SAS/ACCESS & proc sql method

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 456 views
  • 3 likes
  • 3 in conversation