怎么存储过程执行插入的时间比普通插入的时间长？

ofei 2006-08-19 01:35:59

存储过程如下：
CREATE PROCEDURE InsertDemo
@A char(10),
@B char(10),
@C char(10),
@D char(10),
@E char(10),
@F char(10),
@G char(10),
@H char(10),
@I char(10),
@J char(10)
as
set nocount on

insert into demo(A,B,C,D,E,F,G,H,I,J) values(@A,@B,@C,@D,@E,@F,@G,@H,@I,@J)
GO

普通的插入文本如下（{0}-{9}占位）：
insert into Demo(A,B,C,D,E,F,G,H,I,J) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')

测试的程序如下：
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
//using com.db4o;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
FileStream fileStream = File.Open( "File.txt", FileMode.OpenOrCreate );
BufferedStream buffer = new BufferedStream( fileStream );
StreamWriter writer = new StreamWriter( buffer );
//ObjectContainer container = Db4o.OpenFile( "db.yap" );
SqlConnection conn = new SqlConnection( "server=.;database=Test;Integrated Security=SSPI;" );
SqlCommand com = conn.CreateCommand();

conn.Open();
com.CommandText = "InsertDemo";
com.CommandType = System.Data.CommandType.StoredProcedure;

com.Parameters.Add( "@A", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@B", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@C", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@D", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@E", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@F", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@G", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@H", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@I", System.Data.SqlDbType.Char,10 );
com.Parameters.Add( "@J", System.Data.SqlDbType.Char,10 );

for( int i = 0; i < 1000000; i++ )
{
MyObject myobject = new MyObject();
//container.Set( myobject );
//com.CommandText = string.Format( "insert into Demo(A,B,C,D,E,F,G,H,I,J) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
// myobject.A, myobject.B, myobject.C, myobject.D, myobject.E, myobject.F, myobject.G, myobject.H, myobject.I, myobject.J );
com.Parameters[ "@A" ].Value = myobject.A;
com.Parameters[ "@B" ].Value = myobject.B;
com.Parameters[ "@C" ].Value = myobject.C;
com.Parameters[ "@D" ].Value = myobject.D;
com.Parameters[ "@E" ].Value = myobject.E;
com.Parameters[ "@F" ].Value = myobject.F;
com.Parameters[ "@G" ].Value = myobject.G;
com.Parameters[ "@H" ].Value = myobject.H;
com.Parameters[ "@I" ].Value = myobject.I;
com.Parameters[ "@J" ].Value = myobject.J;

com.ExecuteNonQuery();

Console.WriteLine( string.Format( "{0}:{1}", i, DateTime.Now) );
writer.WriteLine( string.Format( "{0}:{1}", i, DateTime.Now));
}

conn.Close();
//container.Close( );
writer.Close();
buffer.Close();
fileStream.Close();
}
}

public class MyObject
{
public MyObject( )
{
}

public string A = "AAAAAAAAAA";
public string B = "BBBBBBBBBB";
public string C = "CCCCCCCCCC";
public string D = "DDDDDDDDDD";
public string E = "EEEEEEEEEE";
public string F = "FFFFFFFFFF";
public string G = "GGGGGGGGGG";
public string H = "HHHHHHHHHH";
public string I = "IIIIIIIIII";
public string J = "JJJJJJJJJJ";
}
}

结果：
存储过程执行用时：17'47''
普通命令执行用时：16'11''
(db4o 5.5 执行用时:13'18'', 先执行db4o插入,再修改程序执行普通插入，再执行存储过程插入)

怎么会这样呢
数据库没做优化
程序以debug方式运行之后，不执行操作其他操作。

...全文