将光标移到/点击文章中的句子上,可以查看译文。      显示繁体中文内容    显示简体中文内容

java - .NET SQL insert slow compared to JDBC? (JDBC 4x faster)
JDBC相比,java. NET SQL插入速度慢 ( JDBC 4x 更快)?

77 0

I done some test between. NET Native SQL Client in C# and compared to the JDBC driver in java and i was blown away by the result, and thinking i must have done something wrong in the. NET code. Result for 1 million inserts in a table with a integer and a text field i empty it before each try, running the programs build for release and by them self.

  • .NET 1 million rows 1 transaction with prepared statements took 159 seconds.
  • java with JDBC driver from microsoft 1 million rows 1 transaction with prepared statments took 39 seconds.

JDBC is 4 times faster Why?

Im kinda stunned that the. NET is so much slower and really hope that i made a big mistake with my. NET code. so anyone have any idea whats wrong or why. NET is so much slower then jdbc?

.NET code :


SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=testameep;User Id=sa;Password=********;");


 con.Open();


 String tempString ="";


 for(int i = 0;i<400;i++)


 {


 tempString +="insert into test1 values (@A" + i.ToString() +",@B" + i.ToString() +" );";


 }



 SqlCommand com = new SqlCommand(tempString,con);


 for (int i = 0; i <400; i++)


 {


 com.Parameters.Add("@A" + i.ToString(), SqlDbType.Int);


 com.Parameters.Add("@B" + i.ToString(), SqlDbType.NVarChar,4000);


 }


 com.Transaction = con.BeginTransaction();


 DateTime start = DateTime.Now;


 com.Prepare();


 for(int i=0;i<2500;i++)


 {


 for (int j=0;j<800;j+=2)


 {


 com.Parameters[j].Value = i;


 com.Parameters[j + 1].Value ="test";


 }


 com.ExecuteNonQuery();


 }


 com.Transaction.Commit();


 label1.Text ="Query took:" + (DateTime.Now - start).TotalMilliseconds +" ms";




java code :


Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver" );


 String dbURL ="jdbc:sqlserver://localhost;databaseName=testameep;user=sa;password=*******";


 Connection dbCon = DriverManager.getConnection( dbURL,"sa","*******" );


 dbCon.setAutoCommit( false );



 String tempString ="";


 for(int i = 0;i<400;i++)


 {


 tempString +="insert into test1 values (?,?);";


 }


 System.out.println("Query start");


 long start = System.currentTimeMillis();


 PreparedStatement ps = dbCon.prepareStatement(tempString);


 for(int i=0;i<2500;i++)


 {


 for (int j=1;j<800;j+=2)


 {


 ps.setInt(j, i);


 ps.setString( j+1,"test");


 }


 ps.executeUpdate();


//ps.addBatch();


 }


//This gave the same speed/result as using ps.executeUpdate();


//ps.executeBatch();


 ps.close();


 dbCon.commit();


 double total = (double)(System.currentTimeMillis() - start)/1000.0;


 System.out.printf("Query stop: %f secondsn", total);




Update : SQL Profiler results. NET:


--Creating the prepared statement. NET


 declare @p1 int


 set @p1=1


 exec sp_prepexec @p1 output,N'@A0 int,@B0 nvarchar(4000),@A1 int,@B1 nvarchar(4000),@A2 int,@B2 nvarchar(4000),@A3 int,@B3 nvarchar(4000),@A4 int,@B4 nvarchar(4000),@A5 int,...(continues)


select @p1



--Inserting rows. NET


exec sp_execute 1,@A0=4,@B0='test',@A1=4,@B1='test',@A2=4,@B2='test',@A3=4,@B3='test',@A4=4,@B4='test',@A5=4,@B5='test',@A6=4,@B6='test',@A7=4,@B7='test',@A8=4,@B8='test',@A9=4,...(continues)


...


2500 rows




SQL Profiler results JDBC :


--Creating the prepared statment JDBC


declare @p1 int


set @p1=1


exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 int,@P5 nvarchar(4000),@P6 int,@P7 nvarchar(4000),@P8 int,@P9 nvarchar(4000),@P10 int,... (continues)


select @p1



--Inserting rows JDBC


exec sp_execute 1,5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5,N'test',5...(continues)


...


2500 rows




时间: 原作者:

84 3

 com.ExecuteNonQuery(); 


 } 


 com.Transaction.Commit(); 




vs


 ps.addBatch(); 


 } 


 ps.executeBatch(); 


 ps.close(); 


 dbCon.commit(); 




Method of execution looks diffent to me

...