使用參數化和塊語句來提高批處理SQL語句的執行效率

如果你的項目要求你的程序對高達幾萬條的數據在集中的時間內執行固定序列的操作，且不能完全使用存儲過程時而需要使用程序來執行時。會需要這些優化。

我們知道，SQL服務器對一條語句的執行，需要分析、編譯、執行這些步驟，通過參數化我們可以對一種命令只分析和編譯一次，而執行多次，從而提高效率。在執行時，如果每次提交語句，可以完成多條SQL語句，則可以減少通訊時間，也可以提高效率。

通過 System.Data.IDbCommand.Prepare() 方法，我們可以在第一次執行語句時，分析和編譯SQL語句，然後保存這個Command對象，下次使用時，直接設置參數執行。這個方法對Oracle和MsSql Server都有效。

如果執行一批語句，在T-SQL和PLSQL中有一點不一樣。

在T-SQL中，多條SQL語句之間，使用分號";"分隔就行。

delete from TableA where id = @id;update TableB set Name=@name where id=@id

而在PLSQL中，則需要用 begin ... end; 包起來，中間語句用分號";"分隔。

begin delete from TableA where id = :id;update TableB set Name=:name where id=:id; end;

相信這樣做之後，你的效率會有幾倍或者十幾倍的提升。當然，你還可以對只是查而修改的表，做一下緩存處理，來減小訪問數據庫的次數。

下面我示例一下訪問Oracle執行PLSQL的一個函數的例子：

private void DeleteFlowInstanceData(string flowinstanceid)
{
　　OracleCommand cmd = this.cmdDeleteFlowInstanceData;
　　if(cmd == null)
　　{
　　　　//生成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;");
　　　　
　　　　//准備 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);
　　
　　　　//准備一下以提高性能。
　　　　cmd.Prepare();
　　}
　　
　　//設置參數
　　cmd.Parameters["instanceId"].Value =　flowinstanceid;
　　
　　//設置事務
　　cmd.Transaction = this.tranEngine;
　　
　　cmd.ExecuteNonQuery();
　　
}