Performance Hit moving to .NET 4.0

Topics: Data Access Application Block
Jun 16, 2011 at 12:41 PM

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
Jun 17, 2011 at 2:14 AM

Hi,

I haven't done any performance testing with DAAB before and to be honest, I'm bit surprised seeing the results. I'm not sure if this issue is really specific to Enterprise Library or with the ADO.NET itself, since DAAB is built on top of ADO.NET. You can log this to our Issue Tracker and let us wait on Microsoft's feedback regarding this.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 17, 2011 at 6:37 PM

Upon some futher testing it is appearing to be DAAB.  And is very isolated on 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 any known way to set this in DAAB?  We are running in a WcfService behind firewalls and this is readonly access to a database so we can assume the security and permissions are fully trusted set before we use DAAB, so it would be nice to be able to use the Assert or LinkDemand if that is possible to set in code or config.  Is this a wcf service setting to run in a more fully trusted environment in 4.0?

We would really like to continue using the DAAB as we connect to both Oracle and SqlServer depending upon what db our client buys, but we cannot afford to have this type of performance issue.

Thanks,

Kurt

Jun 20, 2011 at 1:40 PM

We can now reproduce the same scenario in a console app outside of IIS and ASP.NET.  It seems to be that in .net 3.5 the SqlClientPermissions are done once, in 4.0 it is done on every call to AddInParameters.  This is not bad for one or 5 parameters, but literally we are doing thousands of these in a very large and complicated routine so the difference in performance is very noticable.

Jun 21, 2011 at 2:51 AM

Hi,

I would like to suggest using the service locator to get the database instance but I believe you want to pass the connectionstring at runtime. The workaround I can think of is by using Fluent Configuration API to update the connectionstring. You can refer to this link for more details.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 22, 2011 at 4:01 PM

So you are saying that the SqlClientPermission is a sql connection option that we can set?  Is there a similar setting for Oracle?

Thanks,

Kurt

Jun 23, 2011 at 6:02 AM

I'm just suggesting a workaround without modifying the base code of Enterprise Library. I'm not sure if this can be set since it is already hardcoded on the SqlDatabase class.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 15, 2012 at 6:45 PM

I posted an issue in the issue tracker: http://entlib.codeplex.com/workitem/32858. I am experiencing the same poor performance of Database.AddInParameter, around 100 times as slow as directly creating the parameters using ADO.NET APIs.

I don't understand the workaround you are talking about. This just needs to get fixed in EntLib, period. Its performance is just truly awful.