All geek questions in one place

.NET SQL insert slower compared to JDBC? (JDBC 4 times faster)

I checked some test between the .NET Native SQL Client in C # and compared to the JDBC driver in java, and I was shocked by the result and thought I should do something wrong in the .net code. The result for 1 million inserts in a table with an integer and a text field, I empty it before each attempt, starting the assembly of programs for release and myself.

  • .NET 1 million lines 1 transaction with prepared statements took 159 seconds.
  • java with Microsoft JDBC driver 1 million lines 1 transaction with prepared readings took 39 seconds.

JDBC 4 times faster Why?

I was just stunned that .NET is so slower and actually hopes that I made a big mistake with my .net code. So, does anyone know what is wrong, or why is .NET much slower than 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 seconds\n", total);

Update: SQL Profiler .NET Results:

 --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 JDBC Results:

 --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
+2
source share
1 answer
  com.ExecuteNonQuery(); } com.Transaction.Commit();

against

  ps.addBatch(); } ps.executeBatch(); ps.close(); dbCon.commit();

The execution method looks different to me

0
source

Source: https://habr.com/ru/post/920436/

More articles:


All Articles