Hi Experts,
I am so confuse about push down Operations. how can we differentiate which one Full and which one is Partial pushdown to Improve Performance of Jobs?. Data_Transfer transformation is Full or Partial ?
Please explain me clearly, I have red lot of books and materials but still i am confusing.
Thanks for your help
Venki
ursfriend77 (BOB member since 2011-03-02)
The Data_Transfer transform will funnel all the data from the branch it is in to either a database table or a file, depending on how you have it configured.
The Query transform coded after the Data_Transfer may or may not push the entire SQL query down to the database. It depends on what you’re doing.
The only way to tell for certain if everything is pushed down is to look at the Display Optimized SQL. It’s been a while since I used a Data_Transfer transform so I don’t remember if the Query transform coded downstream from the Data_Transfer will show up in the Display Optimized SQL.
If that isn’t good enough then enable the trace flags such that all SQL commands are recorded in the trace log.
eganjp 
(BOB member since 2007-09-12)
Jim,
DS definitively shows the queries after a DT. For example we use it a lot when we need to find surrogate keys in a big dimension for a relatively small set of data, not too small to prefer a NO_cache hint in the lookup transform.
But I also saw it being misused (in fact overuse) when some developers “believe” that is the solution to all the performance problems.
Regards,
Andrés
aidelia_yahoo 
(BOB member since 2013-07-05)