How to enable/disable Pushdown Optimization

dataservices performance
1

Hi,
I am new to BODS and wanted to understand more about Push down optimization.

  1. Is there’s an option in the Designer tool that prevents pushdown optimization nad instead use the engine even when Pish down optimization is possible? Any inputs will be greatly appreciated.

  2. How to turn on Push down optimization?

thanks
meeta

mbhagra (BOB member since 2013-04-03)

2

There is no neat way to turn on/off a pushdown.

With 12.x(or 4.0) and above, we have something like full pushdown or partial Pushdown.

  1. Full Pushdown - This can happen if both the source/target tables are on the same Datastore/database. (Here data don’t come in the Application layer)
  2. Partial Pushdown is something where the SELECT with ‘n’ joins is alone pushdown to the source system and the data is brought to DS engine and then loaded to Target system

So for you to turn off pushdown I will generally do some Data Services operations that cannot goto any database. For example applying a GEN_ROW_NUM function is the easy one to cancel Full Pushdown…

ganeshxp :us: (BOB member since 2008-07-17)

3

Thx Ganesh. This was helpful!

mbhagra (BOB member since 2013-04-03)

4

You can disable full pushdown by enabling the Bulk Loader. At least it works that way against an Oracle target.

If you put a dummy branch in the Dataflow that would probably also disable full pushdown.

Also, it depends on what level of pushdown you are talking about. Query pushdown or full pushdown. For the former we’re just talking about the query that generates the result set. For the latter it means that the entire Dataflow operation happens in the database.

eganjp :us: (BOB member since 2007-09-12)

5

Bulk Load is a way to turn Full Pushdown to a Partial Pushdown

ganeshxp :us: (BOB member since 2008-07-17)

6

The common semantic is to add a Map_Operation that does not do anything. Everything upstream to it might(!) be pushed down into the database, everything downstream will not.

Obviously there are other settings incompatible with pushdowns, so there are multiple solutions, depending on what you want to push into the database and what you don’t.

Werner Daehn :de: (BOB member since 2004-12-17)

7

Merge transform prevents the pushdown

magesh (BOB member since 2010-01-05)