none
ADO performance in .net 4 vs 3.5 is slower RRS feed

  • Question

  • 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
    Thursday, June 16, 2011 12:53 PM

All replies

  • 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


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Monday, June 20, 2011 9:13 AM
    Moderator
  • We are using SqlDatabase from the DAAB.  We are running in a WCF service hosted in IIS.  We seem to have isolated the slowness to creating parameters, specifically AddInParmeters. One of my developers believes it has to do with the SqlClientPermission attribute using the SecurityAction.Demand, when we run a test using DAAB and create 1000 AddInParameters in 3300ms, when we use ADO.NET directly we do the same code in 21ms. We created our own code to play around with this Attribute and found that if we set it to Assert it does the code in 34ms, Demand is 1580ms, and LinkDemand is 20ms. Reading MSDN says we should not use LinkDemand in 4.0.

    Is there a security change in ASP.NET 4.0 that would affect a WCF service calling an assembly that uses ADO.NET to access a sql server database?

    Thanks,

    Kurt

    Monday, June 20, 2011 12:58 PM
  • Hi Kurt,

    Are you using ASP.NET?  Then the ASP.NET website calls WCF service which calling an assembly using ADO.NET?  If so, I would recommend you consult the issue at the ADO.NET forum queue in ASP.NET forums, http://forums.asp.net/1227.aspx/1?ADO+NET+Entity+Framework+LINQ+to+SQL+NHibernate.   I believe more experts can help you on this issue there. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Tuesday, June 21, 2011 3:42 AM
    Moderator
  • Hi Kurt,

    How's the problem on your side?   Do you get any feedbacks from the ASP.NET forum?

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, June 24, 2011 2:15 AM
    Moderator
  • I was on vacation last week, so this got delayed.

    I just posted it to the forum you recommended...

    http://forums.asp.net/t/1694789.aspx/1?DAAB+Performance+in+ASP+NET+4+0+is+slower+than+in+ASP+NET+2

    Hopefully they will come up with something.

     

    Thanks for your help!

    Kurt

    Tuesday, June 28, 2011 7:59 PM