Using parameterization and block statements to improve the execution efficiency of batch-processing SQL statements

If your project requires your program to perform a fixed sequence of up to tens of thousands of data in a centralized time, and you cannot use the stored procedure completely, you need to use a program to execute it. will require these optimizations.

We know that SQL Server execution of a statement needs to parse, compile, and execute these steps, by parameterization we can parse and compile one command at a time, and execute multiple times to improve efficiency. When executed, if you can complete more than one SQL statement each time you submit a statement, you can reduce the communication time and increase the efficiency.

With the System.Data.IDbCommand.Prepare () method, we can parse and compile the SQL statement the first time the statement is executed, then save the Command object and set the parameter execution directly the next time it is used. This method works for both Oracle and MSSQL servers.

If you execute a batch of statements, there is a bit different in T-SQL and Plsql.

In T-SQL, the semicolon ";" is used between multiple statements. Separated on the line.

Delete from TableA where id = @id; update TableB set name= @name where id= @id

And in the plsql, you need to use the begin ... end; Wrap the middle statement with a semicolon ";" Separated.

Begin DELETE from TableA where id =: id;update TableB set name=:name where Id=:id; End

It is believed that after doing so, your efficiency will be several or more than 10 times times the promotion. Of course, you can also reduce the number of accesses to the database by caching the tables that are only modified.

Here's an example of a function to access Oracle execution Plsql:

private void Deleteflowinstancedata (string flowinstanceid) {OracleCommand cmd = this.cmddeleteflowinstancedata;
　　　　if (cmd = null) {//Generate SQL StringBuilder SB = new StringBuilder (); Sb.
　　
　　　　Append ("Begin"); Sb.
　　　　Append (@ "Delete from Bak_wf_log_enginelog where flowinstanceid=: Instanceid;"); Sb.
　　　　Append (@ "Delete from Bak_wf_log_errlog where flowinstanceid=: Instanceid;"); Sb.
　　　　Append (@ "Delete from Wf_running_msgforenginebak where flowinstanceid=: Instanceid;"); Sb.
　　　　Append (@ "Delete from Bak_wf_running_msgforuser where flowinstanceid=: Instanceid;"); Sb.
　　　　Append (@ "Delete from bak_wf_running_flowactivity where flowinstanceid=: Instanceid;"); Sb.
　　　　Append (@ "Delete from Bak_wf_running_flowdata where flowinstanceid=: Instanceid;"); Sb.
　　
　　　　Append (@ "Delete from bak_wf_running_flowinstance where flowinstanceid=: Instanceid;"); Sb.
　　　　
　　　　Append ("END;");
　　　　Prepare DbCommand this.cmddeleteflowinstancedata = cmd = new OracleCommand (); Cmd. Connection = This.connEngine;
　　　　Cmd.commandtype = CommandType.Text; Cmd.commandtext = sb.
　　　　ToString (); Cmd.
　　　　Parameters.clear (); Cmd.
　　
　　　　Parameters.Add ("Instanceid", oracletype.varchar,250);
　　　　Prepare to improve performance. Cmd.
　　Prepare (); //Set parameter cmd. parameters["Instanceid"].
　　
　　Value = Flowinstanceid; Set up transaction cmd.
　　
　　Transaction = This.tranengine; Cmd.
　　
ExecuteNonQuery (); }