Passthrough SQL?

[tjc]

Using the Query Builder, is there an option that forces EG to always submit pass-through SQL to the target database to avoid accidentally returning the entire table to the EG machine?

[deleted_user]

I also have the same question?

[deleted_user]

As far as I know, there is no parameter for a pass-through in the Query Builder. We couldn't find one and therefore use Proc Sql instead in order to use pass-through's. Such a Proc would look something like this:

proc sql feedback; * The feedback option prints the actual query in the Log;
connect to oracle as dwha_pt(user=MyUsername pw=MyPassword path=MyPath connection=global);
create table work.Base1 as
select *
from connection to dwha_pt(
select f.*
from DWH.f
);
disconnect from dwha_pt;
quit;

Hope this helps. 🙂

[ChrisHemedinger]

The query builder in EG generates SQL that is designed to have a very good chance of passing through to the back end database for processing. It relies on "implicit passthrough", a feature of the SAS/ACCESS database library engines. The implicit passthrough layer optimizes the query for the particular target database, translating the SQL to the dialect that the database would best understand and process.

That said, there are a few operations that can prevent efficient processing with implicit passthrough:
* use of SAS-specific functions within filter expressions (for example STNAME), because the database has no functional equivalent.
* joins of tables where at least one table resides in SAS instead of the database. One remedy: upload the SAS table to the database first. Or if the table is small, simply use the values from the table as literals within the query instead of performing the join.

All of this is designed to optimize the result set brought back from the database into your SAS session. When using SQL, the entire content of the table is never brought back all the way to the EG client machine. EG reads only enough records from the table to display them in the data grid (so, about 40 records at a time)

Chris

[deleted_user]

I note the "very good chance" in your reply Chris, and wonder whether anyone is still working on getting this working better. My (related) issue is better dealt with off line since it isn't E.G. but foundation SAS.

Briefly: a query from MS Access to SQL Server takes under 2 minutes to return a result set matching a small subset from TINY to HUGE. In SAS with pass through, I can't get this under about 50 minutes despite applying every SQL optimisation hint I can find on the web for querying SQL Server. Needless to say, standard SQL code has appalling performance, which was why I was chasing a better outcome while trying to migrate a dog's breakfast of spreadsheets, Access queries, input parameters and SAS code into a single process.

Kind regards

David

[NextVersion]

Yeah I had the same question a while back..
The only way is as stated earlier.. ... open a code node and submit a
proc sql ;

quit;
code with your embeded MSAccess passthrough sql in it..

You are absolutely right about your take on this in a lot of cases it has a possibility of running longer in EG then in other products such as MSAccess who write and enable code that can only run on the SQL at choice...
Also if the function exists locally as well as on the database,, I have found that it still pulls the table locally then runs the sql locally... For large table this is a heavy price to pay and there is no way of adding any options to force it to run on the SQL database.

[deleted_user]

I'm not sure I want to spell this out too much here, it is only vicariously relevant since I am running the code from Foundation SAS, not from E.G.

The intention is to migrate the code to E.G. when it is running more effectively, but we aren't there yet.

I'm reading SQL Server directly, using a SQL Server function to subset little table to littler table, and then joining on a matching key to very big table. Since it is written as passthrough, I don't believe I am seeing data drawn across the network and then matched. The original SAS code offered did that, and to its shame, it took much more than an hour to run.

The original SAS code replaced a tangled process where MS Access was an intermediate step and put the query to SQL Server. That ran in under 2 minutes, but I can't get anywhere near that performance, even after providing explicit index helper statements in the Passthrough code. (Why I should have to tell SQL Server about its own indexes I cannot fathom. However, it did improve the outcome to a small extent.)

I am using all the right syntax with 'Connection to ...", and not seeing the network traffic or process activity I would expect if it were running in SAS instead.

Kind regards

David