In high-frequency data collection, write throughput often determines whether a time-series system can keep up with production data. Parameter binding improves ingestion efficiency by reducing repeated SQL parsing, reusing prepared execution paths, and sending only changing values after the template is prepared.
Performance advantages of parameter binding
Three key advantages:
- Reduced parsing time: Traditional SQL writes require lexical and syntactic analysis of the complete SQL statement each time. Parameter binding uses a pre-compiled SQL template that is parsed only once.
- Pre-compilation optimization: Parameter binding lets the engine pre-compile the SQL, generating optimized execution plans and avoiding repeated query optimization passes.
- Reduced network overhead: Less data is transmitted. The SQL template is sent only once; subsequent transmissions carry only parameter values.
These three optimizations compound in large-volume IoT scenarios. Throughput can improve substantially compared with building SQL strings for every write, especially when the workload repeats the same insert pattern at high volume.
SQL forms for parameter binding
Form 1: Known child table. When the target child table already exists:
INSERT INTO meters (tbname, ts, current, voltage, phase) VALUES(?, ?, ?, ?, ?)
This approach is the simplest and works when child tables are pre-created.
Form 2: Automatic table creation with full columns. Tag columns are included as parameters:
INSERT INTO meters (tbname, ts, current, voltage, phase, location, group_id) VALUES(?, ?, ?, ?, ?, ?, ?)
The database automatically creates child tables based on tag values.
Form 3: Automatic table creation with USING syntax:
INSERT INTO ? USING meters TAGS (?, ?) VALUES (?, ?, ?, ?)
This form separates the child table name, tag values, and data values into distinct parameter groups. The syntax is clearer.
JDBC efficient write features
How it works: When efficient writing is enabled, the connector automatically creates a write thread and dedicated queue:
- The application calls the write interface, placing data into a dedicated queue
- A background write thread retrieves data from the queue
- Data is split and cached by child table
- When a threshold is reached or a timeout occurs, data is sent in batch
This asynchronous batch writing architecture decouples data writing from the application thread and is a core design element for throughput improvement.
Key configuration parameters:
| Parameter | Description | Default |
|---|---|---|
| PROPERTY_KEY_BACKEND_WRITE_THREAD_NUM | Background write thread count | 10 |
| PROPERTY_KEY_BATCH_SIZE_BY_ROW | Batch size (rows) | 1000 |
| PROPERTY_KEY_CACHE_SIZE_BY_ROW | Cache size (rows) | 10000 |
| PROPERTY_KEY_ENABLE_AUTO_RECONNECT | Auto-reconnect enabled | false |
- Thread count: More write threads can increase throughput, but gains depend on CPU, network, server capacity, and table distribution.
- Batch size: Determines rows per network request. Adjust based on available bandwidth.
- Cache size: Maximum cached rows in the queue. Must handle burst traffic.
- Auto-reconnect: Recommended to enable. The connector restores connections automatically after disconnection.
Other features: Standard JDBC PreparedStatement interface support, write timeout configuration, reconnect retry configuration, and write count statistics.
Performance tuning recommendations
- Choose the right SQL form. Prefer Form 1 when child tables exist. The USING syntax (Form 3) is clearer for auto-creation scenarios.
- Configure batch parameters properly. Tune batch size and cache size based on write rate and network conditions.
- Enable auto-reconnect. This is strongly recommended when using efficient write mode.
- Monitor write metrics. Establish monitoring for write performance indicators.
- Understand consistency guarantees. TDengine provides idempotency guarantees but does not provide atomicity across multiple rows in a single write.
Summary
Parameter binding reduces repeated SQL parsing and network payload size. Combined with the JDBC connector’s efficient write mode, it helps developers build higher-throughput ingestion pipelines. The best results come from matching SQL form, batch size, cache size, reconnect behavior, and write-thread count to the actual workload.
