Bulk Updates and Inserts with OPENJSON and CTEs in SQL

Hello, OutSystems developers! If you’ve ever worked with applications handling large datasets, like importing Excel spreadsheets, you know that row-by-row operations can be a performance bottleneck. Endless loops, multiple database connections, and long execution times are common issues. In this article, we’ll explore how to boost performance in OutSystems applications using bulk inserts and bulk updates, leveraging SQL’s JSON functions to create temporary tables via CTEs (Common Table Expressions). I’ll walk through a practical example: creating or updating products from an Excel spreadsheet.

This approach drastically reduces database calls, making your applications faster and more scalable. Let’s dive in!

The Problem: Performance in Large-Scale Data Operations

In OutSystems, it’s common to use actions like CreateOrUpdate in loops to process lists of records. For instance, when importing data from an Excel file, you convert the file into a record list using ExcelToRecordList and iterate over each item. This works fine for small datasets, but for thousands of rows:

Sample

• Each iteration opens a database connection.
• Individual transactions create overhead.
• The risk of timeouts increases.

The result? Slow and inefficient applications. The solution lies in bulk operations: inserting or updating multiple records in a single SQL query.

The Solution: Bulk Operations with JSON and CTEs

OutSystems supports advanced SQL queries, allowing us to use functions like OPENJSON (available in SQL Server 2016+, compatible with many OutSystems environments) to parse JSON data into temporary tables. We then use CTEs to manipulate this data and perform bulk inserts or updates.

General Steps:

1. Prepare Data as JSON: Convert your record list (e.g., from Excel) into a JSON string in batches.
2. Pass to SQL: Use an input parameter in the SQL query to receive the JSON.
3. Parse with OPENJSON: Transform the JSON into a virtual table.
4. Use CTE: Create a common table expression to filter or transform the data.
5. Execute Bulk Operation: Use INSERT INTO … SELECT or UPDATE for inserts or updates.
6. Batch Processing: Process records in batches (e.g., 1000 records at a time) to avoid overwhelming the database server and ensure stability.

This happens in a single transaction per batch, minimizing overhead.

Performance Benefits

• Time Reduction: Tests show up to 70% performance gains compared to traditional loops.
• Fewer Connections: One query per batch instead of N inserts/updates.
• Scalability: Ideal for large datasets, like Excel imports with thousands of rows.
• Consistency: Atomic operations with easy rollback on errors.
• Server Stability: Batching (e.g., 1000 records) prevents excessive memory or CPU usage.

Practical Example: Creating/Updating Products from an Excel Spreadsheet

Imagine a scenario where users upload an Excel spreadsheet containing product data to create new products or update existing ones. Columns: Code (key), Name, Price, Stock. The provided workflow illustrates this process effectively.

Sample 2

Workflow Overview

• Start: Initiates the process.
• ExcelToRecordList: Converts the Excel file into a record list.
• Cycle: Loops through the list, appending records to a batch (e.g., 1000 records).
• GotoSQL: Checks if the batch size equals 1000 or if no records remain.
• ListClear: Clears the local list for the next batch.
• JSONSerialize: Serializes the batch into a JSON string.
• BulkInsert: Inserts new products.
• BulkUpdate: Updates existing products.
• End: Completes the process.

SQL Query for Bulk Update

The BulkUpdate action uses:

• OPENJSON parses the JSON into a table.
• CTE TempProducts creates a virtual temporary table.
• UPDATE … FROM with an INNER JOIN updates existing records based on Code.

SQL Query for Bulk Insert

The BulkInsert action uses:

• OPENJSON parses the JSON into a table.
• CTE TempProducts creates a virtual temporary table.
• INSERT INTO … SELECT with a LEFT JOIN ensures only new records (where Id is NULL) are inserted, avoiding duplicates.

Why Batching Matters

Processing records in batches (e.g., 1000 at a time) is essential for:

• Performance: Smaller transactions reduce lock contention and memory usage.
• Reliability: Prevents server overload, especially with large datasets.
• Error Recovery: If a batch fails, you can log the error and continue with the next batch, isolating the issue.

The workflow’s GotoSQL decision node ensures batches of 1000 records are processed efficiently.

Implementation Tips

• Error Handling: Add TRY-CATCH in the queries to log errors for each batch.
• Validation: Validate the JSON before passing to SQL to avoid parsing issues.
• Batch Size: Adjust the batch size (e.g., 500 or 2000) based on server capacity. 1000 is a good starting point.
• Forge Components: Consider the “BulkInsert” component from the OutSystems Forge for additional abstraction.
• Testing: Monitor database CPU and memory usage; tweak batch sizes if needed.
• Compatibility: This works best with SQL Server. For other databases (e.g., Oracle), use equivalents like JSON_TABLE.

Final Thoughts

Using bulk updates and inserts with JSON, CTEs, and batch processing transforms OutSystems applications from “functional” to “high-performance.” The provided workflow, with BulkInsert and BulkUpdate actions processing 1000 records per batch, exemplifies this efficiency for product imports. Try it in a test environment and see the difference!

Have variations or tips? Share them in the comments below.

Thanks for reading! 🚀