In this article

Overview

Each of the Cloud Data Warehouses (CDW) that SnapLogic supports uses its own SQL Query Optimizer to assess and optimize the SQL queries generated by ELT Pipelines. This is primarily done to reduce the time and costs involved in executing these queries. But, because these optimizers consider optimizing only certain blocks in the query, but not the query as a whole (across these blocks of the query), they generate inefficient query plans and take longer times for execution and incur higher CDW costs. This is due to a generally conservative approach that these CDW optimizers tend to take—so as to not cause data integrity issues.

Consider the following example:

A Pipeline with Snaps—ELT Select → ELT Filter → ELT LIMIT—generates the following nested query without using the SPDO feature.

SELECT * FROM (SELECT * FROM ( SELECT * FROM (SELECT * FROM some_table) alias1 WHERE some_column > 1000) alias2 LIMIT 10) Alias3

As the CDW optimizers look at enhancing the query only at its various chunks but not the entire query, the CDW optimizers may not generate a simpler query like:

SELECT * FROM some_table WHERE some_column > 1000 LIMIT 10

To address this issue of inefficient query optimization, SnapLogic has introduced the Subquery Pushdown Optimization/Elimination (SPDO) feature in all ELT Snaps. Using this feature, the Snaps can read the query across blocks (from different Snaps) and rewrite it to be very close to the second query above (in this example) when the CDW optimizer reads it.

Benefits

SPDO entails the following benefits:

• 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

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 Pipeline Parameter ELT_DISABLE_SUBQUERY_PUSHDOWN to True or true or TRUE.

  Open

  

   

• To enable the SPDO feature back:

  • Change the Pipeline Parameter value to False, false, or FALSE.
    Or

  • Delete the Pipeline Parameter (return to the default behavior).