Archive

Posts Tagged ‘enterprise library’

Performance of in-line SQL vs. stored procedures

May 27, 2011 Leave a comment

Just been doing some load tests with .NET code against SQL Server 2008 R2 Developer edition via Data Access Application Block in Enterprise Library.

I insert 5,000 records into a table with a primary key on an Identity column and a clustered index on the same.  I’m setting 3 nvarchar fields and one smallint one, and then returning the Identity value of the newly added record.  The in-line SQL method calls SELECT SCOPE_IDENTITY() as the second statement in the batch and utilizes the ExecuteScalar() method while the stored proc path merely returns the same value and is called via the ExecuteNonQuery method.  Using caching as detailed below, I almost achieve 1,000 records per second on my developer machine.

I’m afraid I can’t help the stored procedure vs. in-line SQL debate as sometimes the in-line SQL is quicker and other times the stored proc is quicker!

However to get the greatest throughput (on both in-line and stored procs) be sure to cache (or reuse) the following:

  • Database object (from DatabaseFactory.CreateDatabase) – although caching this didn’t make a huge difference
  • DbCommand (needed for next item!)
  • ALL DbParameters – merely overwrite the values on subsequent calls instead of calling Database.AddInParameter

-Krip

Minimum configuration for data access block v5

May 27, 2011 1 comment

If you’re using the Data Access Block from Microsoft’s Enterprise Library (v5, e.g. 5.0.414.0), here is the minimum configuration you’ll need to be able to do data access:

<configuration>

<configSections>

<sectionname=dataConfigurationtype=Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35requirePermission=true />

</configSections>

<dataConfigurationdefaultDatabase=myDbConn>

</dataConfiguration>

<connectionStrings>

<addname=myDbConnconnectionString=data source=(local);Integrated Security= SSPI;Initial Catalog=myDatabase;providerName=System.Data.SqlClient/>

</connectionStrings>

</configuration>

Without this you’re liable to get the error: “The type Database cannot be constructed. You must configure the container to supply this value.”

-Krip