Question Re: Full push down & Partial push down in Technology Q&A

Full push down & Partial push down

Former Member — Thu, 05 Feb 2015 08:42:02 GMT

How to check whether my operation is fully pushed down or partially done ?

View optimized SQL is the only way to check whether the operation i am doing is fully pushed down or partially done ? i have the below in

the SAP article which says

Operations within the SELECT statement that the software can push to the

database include:

• Aggregations — Aggregate functions, typically used with a Group by statement, always produce a data set smaller than or the same size as

the original data set.

My doubt here , If i do the opearions like one query transform to just map from my source to target and another query to do the sorting and another one to group by will have a better performace or combining everything in one query transform will have a better performace ??

Thanks,

AJ.

Re: Full push down & Partial push down

Former Member — Thu, 05 Feb 2015 09:05:51 GMT

You are correct - use "View Optimized SQL" to check how much is being pushed down.

The Data Services optimizer attempts to push down as much of the logic from a data flow as possible, so if logic is distributed across a number of query transforms it can still be combined and pushed down. You can see what is being pushed down by using "View Optimized SQL".

Re: Full push down & Partial push down

former_member187605 — Thu, 05 Feb 2015 09:41:02 GMT

Have you ever looked at ?

Re: Full push down & Partial push down

Former Member — Thu, 05 Feb 2015 13:02:47 GMT

Yes i do and it was writted by you ..but my doubt is just to ensure that if i decouple the aggregation functions from my selelct will it cause a performance drain ? or its goanna be the same ?

In runtime i am not sure how the DS aggregates the group by and order by processes if its distributed across queries

Re: Full push down & Partial push down

Former Member — Thu, 05 Feb 2015 13:37:59 GMT

By using "View Optimized SQL" you will be able to see the extent to which Data Services will combine the logic (distributed across the queries) and push it down.

Re: Full push down & Partial push down

former_member187605 — Thu, 05 Feb 2015 13:42:29 GMT

If you've looked at figure 12 in my document, you've seen that having multiple transforms in a data flow does not prevent DS from pushing the full logic to the database. It really doesn't matter. And that is a general rule.

Re: Full push down & Partial push down

Former Member — Thu, 05 Feb 2015 14:40:32 GMT

Thanks Dirk for the clarification .

Re: Full push down & Partial push down

Former Member — Fri, 13 Feb 2015 19:45:10 GMT

Hi Dirk. What about the batch size handling when implementing full push down operations.

I must ensure the entire operation will fit in the database transaction log?

There´s a way to implement multiple step full push down operation (rows per commit)?

I´m getting transaction log full errors. And I cannot make the tlog match the size of my biggest select-insert push down.

Thanks a lot!

Re: Full push down & Partial push down

former_member187605 — Fri, 13 Feb 2015 20:12:12 GMT

Loading a DWH? Why don't you simply disable logging in your target database? If the ETL process is the only one loading data, logging all those transactions makes no sense. You're never going to do a rollback anyway.

Re: Full push down & Partial push down

Former Member — Fri, 13 Feb 2015 20:22:01 GMT

Thanks Dirk.You are right! But I am on SQLServer... some articles states it is not possible when using it. Must keep reading to find out how!

The db is now in simple recovery model. I am reading if changing to bulk-logged is possible and if it will help...

Re: Full push down & Partial push down

former_member187605 — Fri, 13 Feb 2015 22:45:13 GMT

No luck to you then.

In SQL Server, you may be better off disabling full pushdown and use bulk load to write into your database.

Re: Full push down & Partial push down

Former Member — Thu, 19 Feb 2015 18:29:07 GMT

Thanks, Dirk!

I ended looking for full push down trying to optimize a data flow that takes about an hour to complete. With full push down and some dataflow splitting, it takes only 20 minutes (Simple recovery model). Other thing I have also tried is just the collect/use statistics for optimization strategy. This works (20 minutes duration too), but since I´m working in just a small piece of a bigger job, it´s not recommended by my colleagues. I´m reading to try to understand how to manually optimize caches the same way the collect/use statistics do. Is just matter of playing with cache activation/deactivation, sizes and types? Thanks again.

Re: Full push down & Partial push down

former_member187605 — Thu, 19 Feb 2015 19:31:04 GMT

Statistics influence cache type only, DS chooses between in-memory and pageable. That doesn't matter when all is pushed down to the database, no need for caching then.