.net 4 vs 3.5中的ADO性能较慢 [英] ADO performance in .net 4 vs 3.5 is slower

问题描述

我们正在运行一个托管在IIS中的WCF服务，当我们将代码移到.NET 4时，我们注意到使用Entity Framework DAAB和可能的ADO.NET 4.0，性能大幅下降。我们看到两个领域的表现正在下降。数据库
AddInParameter有点慢，大约5-10％，具体取决于sql语句的参数数量，但ExecuteReader慢15-20％。我们的服务是对非常大的数据库上的大量数据进行跟踪，并使用许多参数执行大量复杂的
查询。

一个非常简单的性能示例是下面的代码：我已经简化了只使用SQL的代码，我们通常有一个确定SQL Server或Oracle的工厂，但是性能问题似乎并不重要所以我试图消除代码来计算性能问题
。

数据库fin =  new  SqlDatabase（connectionString）; 
 DbCommand cmd = fin.GetSqlStringCommand（ @" SELECT * FROM IC_LOT_TRACE 
 WHERE COMPANY_CODE = @ companyCode AND IC_LT_TO_INDICATOR ='I'
 AND IC_LT_TO_DIV_WH_FA = @ warehouse AND IC_LT_TO_DOC_PART_JOB = @ partCode 
 AND IC_LT_TO_LINE_LOT_STAGE = @lotNumber AND IC_LT_TO_STATUS ='QOH'
 ORDER BY TRANSACTION_DATE，SYSTEM_DATE" ）; 
 fin.AddInParameter（cmd，" companyCode" ，DbType.AnsiStringFixedLength，companyCode）; 
 fin.AddInParameter（cmd，" warehouse" ，DbType.AnsiStringFixedLength，仓库）; 
 fin.AddInParameter（cmd，" partCode" ，DbType.AnsiStringFixedLength，partCode）; 
 fin.AddInParameter（cmd，" lotNumber" ，DbType.AnsiStringFixedLength，lotNumber）; 
 
 使用（ var  reader = fin.ExecuteReader（cmd））
 {
  while （reader.Read（））
 {
 lblTransactionDate.Text = reader [" TRANSACTION_DATE" ] == DBNull.Value 
？ DateTime.Now.ToString（）
：Convert.ToDateTime（reader [" TRANSACTION_DATE" ]）。ToString（）; 
} 
} 
 

 .net 3.5 / ADO.NET 2.0中的4个插件参数需要5.843ms，需要6.957在4.0中的ms。执行阅读器在3.5中占用34.396ms，在4.0中占用42.884ms。

是的，这些是ms，我的时间测试是一个非常小的数据库，但执行时在大型数据库上有几百个sql语句我们看到了很大的性能损失。

谢谢，

 Kurt 

解决方案

嗨Kurt，

欢迎来到MSDN论坛！

问题很有趣。 我之前看不到ADO.NET 3.5和ADO.NET 4.0之间存在很大的性能差异。 所以我尝试检查ADO.NET 3.5和ADO.NET 4.0之间的SqlDataReader（）方法的详细实现，但是，
我没有发现很多重大变化。 

此外，您能否告诉我们您使用的是哪个数据库类？ 它是由您自己编写还是在一些现有的.NET程序集中编写？ 另外，如果您使用的是大型数据库，您是否介意给我们多少性能差异？ 

美好的一天！

谢谢

We are running a WCF Service hosted in IIS and when we moved our code to .NET 4 we noticed a substantial decrease in performance using the Entity Framework DAAB and possibly ADO.NET 4.0. We are seeing two areas that are decreasing in performance. The Database AddInParameter is a little bit slower, around 5-10% depending upon the number of parameters to a sql statement, but the ExecuteReader is 15-20% slower. Our service is performing a trace over a lot of data on very large databases and executing numerous complicated queries with many parameters.

A very simple example of the performance is the code below: I have simplified the code to use only SQL, we usually have a factory that determines SQL Server or Oracle, but the performance issues don't seem to matter so I am trying to eliminate code to figure out the performance issues.

      Database fin = new SqlDatabase(connectionString);
      DbCommand cmd = fin.GetSqlStringCommand(@"SELECT * FROM IC_LOT_TRACE 
          WHERE COMPANY_CODE=@companyCode AND IC_LT_TO_INDICATOR='I' 
          AND IC_LT_TO_DIV_WH_FA=@warehouse AND IC_LT_TO_DOC_PART_JOB=@partCode 
          AND IC_LT_TO_LINE_LOT_STAGE = @lotNumber AND IC_LT_TO_STATUS='QOH' 
          ORDER BY TRANSACTION_DATE, SYSTEM_DATE");
      fin.AddInParameter(cmd, "companyCode", DbType.AnsiStringFixedLength, companyCode);
      fin.AddInParameter(cmd, "warehouse", DbType.AnsiStringFixedLength, warehouse);
      fin.AddInParameter(cmd, "partCode", DbType.AnsiStringFixedLength, partCode);
      fin.AddInParameter(cmd, "lotNumber", DbType.AnsiStringFixedLength, lotNumber);

      using (var reader = fin.ExecuteReader(cmd))
      {
        while (reader.Read())
        {
          lblTransactionDate.Text = reader["TRANSACTION_DATE"] == DBNull.Value
                         ? DateTime.Now.ToString()
                         : Convert.ToDateTime(reader["TRANSACTION_DATE"]).ToString();
        }
      }

The 4 addin parameters take 5.843ms in .net 3.5/ADO.NET 2.0 and take 6.957ms in 4.0. The execute reader takes 34.396ms in 3.5 and takes 42.884ms in 4.0.

Yes these are ms and my testing for timings was a very small db, but when executed several hundred sql statements on a large db we see a very big performance hit.

Thanks,

Kurt

解决方案

Hi Kurt,

Welcome to MSDN forum!

The question is interesting.  I don't see quite large performance different between ADO.NET 3.5 and ADO.NET 4.0 before.  So I tried to check the detailed implementation of the SqlDataReader.Read() method between ADO.NET 3.5 and ADO.NET 4.0, however, I did not find many big change. 

Besides, could you please let us know which Database class are you using?  Is it written by yourself or in some existing .NET assemblies?  Also, would you mind letting us how many performance difference if you are using some big database? 

Good day!

Thanks

这篇关于.net 4 vs 3.5中的ADO性能较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！