.NET的SQL插入慢JDBC相比? (JDBC 4倍快) [英] .NET SQL insert slow compared to JDBC? (JDBC 4x faster)
问题描述
我在C#.NET进行本地SQL客户端之间的一些测试和比较,Java中的JDBC驱动程序,我被吹走的结果,以为我一定是做了错误的.NET code。 结果100万插入表中的一个整数和一个文本字段我以前每次尝试清空,运行的程序构建和发布他们的自我。
- 在.NET 1百万行1交易与prepared声明拿了159 秒。
- 在Java中使用JDBC驱动程序,从微软1万行1事务 其中prepared statments花了39秒。
更快 JDBC是4倍,为什么?
林有点愣了.NET是如此慢得多,真希望我犯了一个大错我的.NET code。因此,任何人有任何想法,什么是错,或者为什么.NET是这么多慢然后JDBC?
.NET code:
的SqlConnection CON =新的SqlConnection(数据源=本地主机;初始目录= testameep;用户ID = SA;密码= ********;);
con.Open();
字符串tempString =;
的for(int i = 0; I< 400;我++)
{
tempString + =插入test1的值(@A+ i.ToString()+,@ B+ i.ToString()+);
}
SqlCommand的COM =新的SqlCommand(tempString,CON);
的for(int i = 0; I< 400;我++)
{
com.Parameters.Add(@ A+ i.ToString(),SqlDbType.Int);
com.Parameters.Add(@ B+ i.ToString(),SqlDbType.NVarChar,4000);
}
com.Transaction = con.BeginTransaction();
日期时间开始= DateTime.Now;
。com prepare();
的for(int i = 0; I< 2500;我++)
{
对于(INT J = 0; J< 800; J + = 2)
{
com.Parameters [J]。价值=我;
com.Parameters [J + 1] .value的=测试;
}
com.ExecuteNonQuery();
}
com.Transaction.Commit();
label1.Text =查询了:+(DateTime.Now - 启动).TotalMilliseconds +毫秒;
Java的code:
的Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver);
字符串dbURL =的jdbc:SQLSERVER://本地主机,数据库名= testameep;用户= SA;密码= *******;
连接dbCon =的DriverManager.getConnection(dbURL,山,*******);
dbCon.setAutoCommit(假);
字符串tempString =;
的for(int i = 0; I< 400;我++)
{
tempString + =插入test1的值(?,?);
}
的System.out.println(查询开始);
长时间启动= System.currentTimeMillis的();
。preparedStatement PS = dbCon prepareStatement(tempString);
的for(int i = 0; I< 2500;我++)
{
对于(INT J = 1; J< 800; J + = 2)
{
ps.setInt(J,I);
ps.setString(J + 1,测试);
}
ps.executeUpdate();
// ps.addBatch();
}
//这给了相同的速度/结果作为使用ps.executeUpdate();
//ps.executeBatch();
ps.close();
dbCon.commit();
双总=(双)(System.currentTimeMillis的() - 开始)/ 1000.0;
System.out.printf(查询站:%F秒\ N,总);
更新: SQL事件探查结果 .NET:
- 创建prepared声明.NET
声明@ P1 INT
设置@ P1 = 1
EXEC以sp_ prepexec @ P1输出,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,......(继续)
选择@ P1
--Inserting行.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行
SQL事件探查结果 JDBC:
- 创建prepared说明书JDBC
声明@ P1 INT
设置@ P1 = 1
EXEC以sp_ prepexec @ P1输出,N'@ P0 INT,@ P1为nvarchar(4000),@ P2 INT,@ P3为nvarchar(4000),@ P4 INT,@ P5为nvarchar(4000),@ P6 INT,@ P7为nvarchar(4000),@ INT P8,P9 @为nvarchar(4000),@ P10 INT,......(继续)
选择@ P1
--Inserting行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行
com.ExecuteNonQuery();
}
com.Transaction.Commit();
VS
ps.addBatch();
}
ps.executeBatch();
ps.close();
dbCon.commit();
执行方法看起来diffent我
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 seconds\n", 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
com.ExecuteNonQuery();
}
com.Transaction.Commit();
vs
ps.addBatch();
}
ps.executeBatch();
ps.close();
dbCon.commit();
Method of execution looks diffent to me
这篇关于.NET的SQL插入慢JDBC相比? (JDBC 4倍快)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!