This article is in BETA State.
In this article
| Table of Contents | ||||||
|---|---|---|---|---|---|---|
|
...
As the CDW optimizers look at enhancing the query only at its various chunks but not the entire query, they MAY NOT generate the simple query that it actually isthe CDW optimizers may not generate a simpler query like:
SELECT * FROM some_table WHERE some_column > 1000 LIMIT 10
...
Cover for the shortcomings of the CDW optimizer and thus improve performance in execution for some cases.
Reduce the execution cost.
Reduce the derived table Subquery footprint.
Improve query readability and hence the ease of debugging failed queries.
Reduce the query size.
How to
...
enable this feature
There are two ways to use the SPDO feature in your Pipelines.
Using a Pipeline Parameter at a Pipeline level
Using Pipeline Parameters at a Snap level
Using a Pipeline Parameter at a Pipeline level
To enable the SPDO feature, define the Pipeline Parameter ELT_DISABLE_SUBQUERY_PUSHDOWN with the value False, false, or FALSE.
...
Starting from 4.28 GA, all ELT Pipelines perform SPDO, by default.
In case you do not want to use the SPDO feature, set the value for the same Pipeline Parameter ELT_DISABLE_SUBQUERY_PUSHDOWN to True or true or TRUE.
Using Pipeline Parameters at a Snap level
In addition to the above Pipeline Parameter at the Pipeline Level, SnapLogic allows you to choose using SPDO at a Snap level for certain Snaps. Each Snap has a unique Pipeline Parameter that you can use to toggle the SPDO feature anytime before running the Pipeline. See the table below for this list.
...
Snap
...
Parameter to toggle SPDO
...
To enable SPDO
...
To Disable SPDO
...
ELT Aggregate
...
ELT_DISABLE_AGGREGATE_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Filter
...
ELT_DISABLE_FILTER_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Limit
...
ELT_DISABLE_LIMIT_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Pivot
...
ELT_DISABLE_PIVOT_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Sample
...
ELT_DISABLE_SAMPLE_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Sort
...
ELT_DISABLE_SORT_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Transform
...
ELT_DISABLE_TRANSFORM_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Unique
...
ELT_DISABLE_UNIQUE_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Unpivot
...
ELT_DISABLE_UNPIVOT_SUBQUERY_PUSHDOWN
...
False
...
True
...
ELT Window Functions
...
ELT_DISABLE_WINDOW_FUNCTIONS_SUBQUERY_PUSHDOWN
...
False
...
True
To enable the SPDO feature back:
Change the Pipeline Parameter value to False, false, or FALSE.
OrDelete the Pipeline Parameter (return to the default behavior).
For more information on which upstream Snaps can pushdown push down subqueries into a given Snap, refer to the respective Snap document.