Performance Issue: Slow Execution of Security Integration

Issue Summary:

The security integration process is experiencing performance issues, taking approximately 40–45 minutes to load data into a Snowflake table.

• File Format: CSV
• Record Count: 11,000+
• Field Count: 230
• Current Settings: Query Batch Size = 1000, Commit Count = 1000
• Comparison: Other integrations with around 120 columns work efficiently.
• Other Observations:
  • TAXLOT integration (66k records, batch/commit = 1000): ~60 insert statements.
  • SECURITY integration (3k records, batch/commit = 1000): ~1000+ insert statements.

Root Cause Analysis:

Snowflake incorporates an internal performance safeguard that can dynamically shift from batch processing to row-by-row execution when memory thresholds are exceeded.

If the system detects high memory usage (often influenced by a high column count or batch size), it automatically switches to processing records one at a time—greatly increasing overall execution time.

Testing and Observations:

To validate this behavior, we conducted some tests:

Test Scenario 1:

Inserted records using batch size = 1000, with 232 columns.

Result: First batch inserted successfully; subsequent records were processed one by one.

Test Scenario 2:

Reduced the number of columns while keeping batch size = 1000.

Result: Batch execution worked as expected.

Test Scenario 3:

Maintained all 230+ columns but reduced batch size to 250.

Result: Batch processing worked as expected and performance improved.

Conclusion:

These observations indicate that the behavior is specific to Snowflake’s internal processing rather than Adeptia.

Therefore, it is recommended to adjust the batch size based on the structure of your tables and Snowflake’s handling capacity. 

Recommended Actions:

• Reduce the Query Batch Size in the Advance database Layout and test the execution.
• The optimal batch size may vary depending on your schema, so we recommend gradually decreasing the value to identify a stable configuration.
• Also, ensure that the values of Query Batch Size and Commit Count are the same—for example, both set to 500 or 250.

Client Feedback:

• Successfully tested with batch/commit size = 250 – data load time reduced to approximately 3 minutes.
• Also tested with sizes of 200 and 300:
  • 200: Performed well but slightly slower than 250.
  • 300: Performance degradation observed.
• Previous attempts with 100, 500, 1000, and 2000 resulted in significantly longer execution times.