Ingesting Data in Parameter Binding Mode

import Tabs from "@theme/Tabs"; import TabItem from "@theme/TabItem";

When inserting data using parameter binding, it can avoid the resource consumption of SQL syntax parsing, thereby significantly improving the write performance. The reasons why parameter binding can improve writing efficiency include:

• Reduced parsing time: With parameter binding, the structure of the SQL statement is determined at the first execution, and subsequent executions only need to replace parameter values, thus avoiding syntax parsing each time and reducing parsing time.
• Precompilation: When using parameter binding, the SQL statement can be precompiled and cached. When executed later with different parameter values, the precompiled version can be used directly, improving execution efficiency.
• Reduced network overhead: Parameter binding also reduces the amount of data sent to the database because only parameter values need to be sent, not the complete SQL statement, especially when performing a large number of similar insert or update operations, this difference is particularly noticeable.

It is recommended to use parameter binding for data insertion.

:::note We only recommend using the following two forms of SQL for parameter binding data insertion:

```sql
a. Ensure that the subtable exists. Not adding a tag can improve write performance. (If the subtable does not exist, this behavior is inconsistent with Taos Shell. Taos Shell automatically creates a table with a tag of NULL, and stmt reports an error to prevent accidental table creation due to incorrect table name setting)
   1. INSERT INTO meters (tbname, ts, current, voltage, phase) VALUES(?, ?, ?, ?, ?) 
b. The subtable does not exist, specify the tag to automatically create the subtable:
   1. INSERT INTO meters (tbname, ts, current, voltage, phase, location, group_id) VALUES(?, ?, ?, ?, ?, ?, ?)   
   2. INSERT INTO ? USING meters TAGS (?, ?) VALUES (?, ?, ?, ?)
```

:::

Next, we continue to use smart meters as an example to demonstrate the efficient writing functionality of parameter binding with various language connectors:

1. Prepare a parameterized SQL insert statement for inserting data into the supertable meters. This statement allows dynamically specifying subtable names, tags, and column values.
2. Loop to generate multiple subtables and their corresponding data rows. For each subtable:
   • Set the subtable's name and tag values (group ID and location).
   • Generate multiple rows of data, each including a timestamp, randomly generated current, voltage, and phase values.
   • Perform batch insertion operations to insert these data rows into the corresponding subtable.
3. Finally, print the actual number of rows inserted into the table.

WebSocket Connection

There are two kinds of interfaces for parameter binding: one is the standard JDBC interface, and the other is an extended interface. The extended interface offers better performance.

{{#include docs/examples/JDBC/JDBCDemo/src/main/java/com/taos/example/WSParameterBindingStdInterfaceDemo.java:para_bind}}

{{#include docs/examples/JDBC/JDBCDemo/src/main/java/com/taos/example/WSParameterBindingExtendInterfaceDemo.java:para_bind}}

This is a more detailed parameter binding example

The following is an example code for using stmt2 to bind parameters (applicable to Python connector version 0.5.1 and above, and TDengine v3.3.5.0 and above):

{{#include docs/examples/python/stmt2_ws.py}}

The example code for stmt to bind parameters is as follows:

{{#include docs/examples/python/stmt_ws.py}}

{{#include docs/examples/rust/restexample/examples/stmt.rs}}

{{#include docs/examples/node/websocketexample/stmt_example.js}}

{{#include docs/examples/c-ws-new/stmt2_insert_demo.c}}

Native Connection

{{#include docs/examples/JDBC/JDBCDemo/src/main/java/com/taos/example/ParameterBindingBasicDemo.java:para_bind}}

This is a more detailed parameter binding example

{{#include docs/examples/python/stmt2_native.py}}

The example code for binding parameters with stmt2 (Go connector v3.6.0 and above, TDengine v3.3.5.0 and above) is as follows:

{{#include docs/examples/go/stmt2/native/main.go}}

The example code for binding parameters with stmt is as follows:

{{#include docs/examples/go/stmt/native/main.go}}

{{#include docs/examples/rust/nativeexample/examples/stmt.rs}}

The example code for binding parameters with stmt2 (TDengine v3.3.5.0 or higher is required) is as follows:

{{#include docs/examples/c/stmt2_insert_demo.c}}

The example code for binding parameters with stmt is as follows (TDengine v3.3.5.0 has stopped maintenance):

{{#include docs/examples/c/stmt_insert_demo.c}}