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

[siddhu1]

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

[Quentin]

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.

[PeterClemmensen]

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