Ability to define the query chunk size for large bulk data transactions
Currently, if you are importing say – 1 million records, SmartConnect runs the query in its entirety into memory and starts writing to the target system. This isn’t ideal for situations that have a large number of source records. Memory limits get reached and the software becomes unresponsive.
Competitors allow you to dictate how large the “chunks” will be when querying the source data. For example, if your source data is a SQL query that returns 800,000 records, you can define a threshold of 5,000 records that are read and written at a time.
Currently, the only way to achieve this is to modify your source data to select the top 5,000, or somehow restrict your query and continually update it. You could also create multiple data sources, multiple integrations, and attempt to schedule them out – but again, definitely not ideal – particularly since customers often like these jobs to be run at night – and who wants to be up at 3am modifying queries? (I know I don’t).
So to reiterate the use case:
- I have 1 million records
- I want to be able to set a query threshold of 5,000 (or x number)
- I want smart connect to query 5,000, then write 5,000, query 5,000 then write 5,000, etc. etc.
I want to do this because I have limitations on memory and want to avoid constantly manipulating source data queries during off hour times.
If you would like to submit an answer or comment, please sign in to the eOne portal.