topic Re: Implict or explicit SQL pass through - Efficiency wise accessing large datasets in SAS Programming

Implict or explicit SQL pass through - Efficiency wise accessing large datasets

siddhu1 — Mon, 07 Feb 2022 14:43:41 GMT

Hi,

In accessing large datasets and modifying,which is the efficient way of using the implicit and explicit SQL pass through facility in connecting to the database.
Can anyone suggest any tips on this for the efficiency of the queries.

Thanks & Regards,

Siddhartha

Re: Implict or explicit SQL pass through - Efficiency wise accessing large datasets

Quentin — Mon, 07 Feb 2022 15:11:18 GMT

Implicit pass-through will take your SAS code, and translate it into the native SQL of the database to execute it.

With explicit pass-through you get to write the native SQL of the database.

I wouldn't expect there to be much of an efficiency difference, if you write the same native SQL with the explicit pass-through that SAS generates with implicit pass-through.

But typically, people are better at writing code than computers. If you are experienced in the native SQL of the database, and the optimizer the database uses, etc., then using explicit pass-through will give you more ability to write code that is more optimized for efficiency. Also, explicit pass-through avoids the risk inherent to implicit pass-through, that risk being that SAS will decide it cannot translate your code the native SQL of the database and will have to transfer all of the source data for processing in SAS.

Re: Implict or explicit SQL pass through - Efficiency wise accessing large datasets

PeterClemmensen — Mon, 07 Feb 2022 18:10:51 GMT

I highly recommend reding Tactics for Pushing SQL to the Relational
Databases 🙂