In-DB Batch Macro

[andrewdatakim]

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.  

[SeanAdams]

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