Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

In-DB Batch Macro

I would like to see In-DB batch macros, currently we are joining tables with 30 million+ records and we are having to run it through standards tools because we are unable to process via In-DB, which has a 20% improvement in processing speed based on the peformance profiling.  

2 Comments
SeanAdams
17 - Castor
17 - Castor

Depending on your database - this may be possible with a stored procedure or row limiters.  The challenge with in-db is that the query is running in the native dialect of every different database and some do not support different SQL features.

 

 

For example: in ms sql (T-SQL) you can use cursors as a loop to do this but it does require some knowledge of t-SQL to do this

 

One way that you can use to do this which is relatively transportable across any SQL platform is to use row limits and flags.

 

Say you wanted to update the value of a share portfolio by updating current market prices and exchange rates.  Add a "last updated date" column, then set the tow count to 10 000 and update records where the last updated date is more than 24 hrs old.   You can then run this job as in-db and it will continuously update in small batches.

 

There are lots of variants of this idea using flags or joins or row limits to do what you are looking for, all fully in-db, and by doing it in smaller batches you also reduce locking contention so that other users dont get a timeout while trying to read the data during an update that takes an hour

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes