1. Comparison of efficiency between SQL statement execution and direct execution of stored procedures
1) spell SQL statements in the program code, similar code:
| The code is as follows: |
Copy code |
System. Data. OleDb. OleDbCommand Command = conn. CreateCommand ();
Command. CommandTimeout = 0;
Command. Transaction = trans;
Command. CommandType = CommandType. Text;
String SQL = "";
For (int I = 0; I <IWo_Img.Count; I ++)
{
Wo_Img = IWo_Img [I] as EMS _EDI_WO_IMG;
Wo_ImgDb.Wo_No = Wo_Img.Wo_No;
Wo_ImgDb.Comp_No = Wo_Img.Comp_No;
Wo_ImgDb.Take_Date _ = Wo_Img.Take_Date;
Wo_ImgDb. EMS _No = EMS _no;
Wo_ImgDb.Process_No = Wo_Img.Process_No;
Wo_ImgDb.Item_No _ = Wo_Img.Item_No;
Wo_ImgDb.Take_Type _ = Wo_Img.Take_Type;
Wo_ImgDb.Qty _ = Wo_Img.Qty.ToString ();
Wo_ImgDb.Unit _ = Wo_Img.Unit;
Wo_ImgDb.Pcno = pcsum; SQL = "insert into EMS _EDI_WO_IMG (WO_NO, COMP_NO, Take_Date, PROCESS_NO, Item_No, Take_Type, Qty, Unit, EMS _NO, PCNO)" +
"Values ('" + Wo_ImgDb.Wo_No + "', '" + Wo_ImgDb.Comp_No + "', '" + comment _ + "', '" + Wo_ImgDb.Process_No + "', '"+ Wo_ImgDb.Item_No _ +"', '"+
Wo_ImgDb.Take_Type _ + "','" + Wo_ImgDb.Qty _ + "','" + Wo_ImgDb.Unit _ + "','" + Wo_ImgDb. EMS _No + "', '"+ Wo_ImgDb.Pcno +"' "+
")";
Command. CommandText = SQL;
Command. ExecuteNonQuery ();
This. m_Actor.ShowCurrentValue (j + 1 );
J = j + 1;
If (j % 1000 = 0) & (j! = 0 ))
{
This. m_Actor.ShowMessage ("1000 ticket receiving information data imported successfully! ");
}
}
Command. Dispose ();
} |
Execute 5 times, restart the database every time you execute, and import 8000 pieces of data each time. The execution time is:
First time: 0 minutes 2 seconds, 609 MS
Second time: 0 minutes 2 seconds, 375 ms
Third time: 0 minutes 2 seconds, 421 MS
Fourth time: 0 minutes 2 seconds, 562 MS
Fifth time: 0 minutes 2 seconds, 437 MS
2) use a stored procedure to insert data. The code is as follows:
| The code is as follows: |
Copy code |
System. Data. OleDb. OleDbCommand Command = conn. CreateCommand ();
Command. CommandTimeout = 0;
Command. Transaction = trans;
Command. CommandType = CommandType. StoredProcedure;
Command. CommandText = "SP_InsWOImgList ";
System. Data. OleDb. OleDbParameter [] parameters = {
New System. Data. OleDb. OleDbParameter ("wono", OleDbType. VarChar, 50 ),
New System. Data. OleDb. OleDbParameter ("tradeCo", OleDbType. VarChar, 10 ),
New System. Data. OleDb. OleDbParameter ("takeDate", OleDbType. VarChar, 12 ),
New System. Data. OleDb. OleDbParameter ("processNo", OleDbType. VarChar, 50 ),
New System. Data. OleDb. OleDbParameter ("itemNo", OleDbType. VarChar, 50 ),
New System. Data. OleDb. OleDbParameter ("takeType", OleDbType. VarChar, 10 ),
New System. Data. OleDb. OleDbParameter ("qty", OleDbType. VarChar, 50 ),
New System. Data. OleDb. OleDbParameter ("unit", OleDbType. VarChar, 10 ),
New System. Data. OleDb. OleDbParameter ("pcno", OleDbType. Integer, 4 ),
New System. Data. OleDb. OleDbParameter ("emsno", OleDbType. VarChar, 12 )}; For (int I = 0; I <IWo_Img.Count; I ++)
{
Wo_Img = IWo_Img [I] as EMS _EDI_WO_IMG;
Command. Parameters. Clear ();
Wo_ImgDb.Wo_No = Wo_Img.Wo_No;
Wo_ImgDb.Comp_No = Wo_Img.Comp_No;
Wo_ImgDb.Take_Date _ = Wo_Img.Take_Date;
Wo_ImgDb. EMS _No = EMS _no;
Wo_ImgDb.Process_No = Wo_Img.Process_No;
Wo_ImgDb.Item_No _ = Wo_Img.Item_No;
Wo_ImgDb.Take_Type _ = Wo_Img.Take_Type;
Wo_ImgDb.Qty _ = Wo_Img.Qty.ToString ();
Wo_ImgDb.Unit _ = Wo_Img.Unit;
Wo_ImgDb.Pcno = pcsum;
Parameters [0]. Value = Wo_ImgDb.Wo_No;
Parameters [1]. Value = Wo_ImgDb.Comp_No;
Parameters [2]. Value = Wo_ImgDb.Take_Date _;
Parameters [3]. Value = Wo_ImgDb.Process_No;
Parameters [4]. Value = Wo_ImgDb.Item_No _;
Parameters [5]. Value = Wo_ImgDb.Take_Type _;
Parameters [6]. Value = Wo_ImgDb.Qty _;
Parameters [7]. Value = Wo_ImgDb.Unit _;
Parameters [8]. Value = Wo_ImgDb.Pcno;
Parameters [9]. Value = Wo_ImgDb. EMS _No;
For (int k = 0; k <10; k ++)
{
Command. Parameters. Add (parameters [k]);
}
Command. ExecuteNonQuery ();
This. m_Actor.ShowCurrentValue (j + 1 );
J = j + 1;
If (j % 1000 = 0) & (j! = 0 ))
{
This. m_Actor.ShowMessage ("1000 ticket receiving information data imported successfully! ");
}
}
Command. Dispose ();
} |
Execute 5 times, restart the database every time you execute, and import 8000 pieces of data each time. The execution time is:
First time: 0 minutes 1 second, 531 MS
Second time: 0 minutes 1 second, 656 MS
Third time: 0 minutes 1 second, 533 MS
Fourth time: 0 minutes 1 second, 542 MS
Fifth time: 0 minutes 1 second, 551 MS
Stored procedure execution/SQL statement execution in the program
7813/12404 = 62.99%
SQL statement execution/stored procedure execution in the program
12404/7813 = 158.76%
Finally, it is concluded that the execution efficiency of stored procedures is higher than that of SQL statements in programs. However, if the total execution time is long, such as 10 minutes or 20 minutes, this saves time.
The user may not feel much, for example, from 20 minutes to 12-13 minutes. Only when the original import time is 2 hours,
Now, we only need a little more than an hour. If the import time is less than 20 minutes, if performance and efficiency are not required,
Either method is similar.
