Interactives with In-DB and Filters

[kalvinharris]

For one of our workflows, we designed an interactive app that would be able to let us have an in-DB filter or just a general select depending on what the data need is. Using a tickbox it would delete the connected tools and run through a union. 

We noticed recently in the debug that the filter into the single source union makes the workflow run very slow with our Oracle connection. Is there any way to change the structure of the workflow not use unions 

[StellaBon]

Maybe put each in a container, make a macro, attach the app to the macro's outer workflow to select one container or the other. 

I always use the below as a template. Good question!

[kalvinharris]

That wouldn't really work for what we need. This screenshot is only a small portion of the workflow and there are multiple other joins and filters that use the same logic with the discretionary filters or selects. 

[StellaBon]

@kalvinharris I find that with the In-Db tools, the Joins and Unions can slow things down considerably. The few fixes I have found are reducing the data in Joins/Unions to only necessary fields, make sure there is no LOB data or other heavy lifts. Also I find that if you are Creating a new table it is way slower than if you are Appending or Drop/replacing, so that is another possible tweak. 

[danilang]

HI @kalvinharris 

Everything in an IN-DB sequence happens on the back end database until you you use a Data Stream Out Tool.  If the process is slowing down when you're using the Filter In-DB, it's probably because you're attempting to filter on one or more fields that don't have indexes set.  Use a Dynamic Output In-DB to output the final SQL query and sit down with your database team to see how the query can be optimized

Dan