Performance passing DbParameter

Topics: Data Access Application Block
Mar 30, 2007 at 9:24 PM
This code take 1 second in time:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("SELECT desc FROM tab where id='0'");
 
for (int i = 0; i < 1000; ++i)
   ret = (string)db.ExecuteScalar(dbCommand);
dbCommand.Dispose();

This take 10 seconds:
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("SELECT desc FROM tab where id=@id");
db.AddInParameter(dbCommand, "id", DbType.String, "0");
 
for (int i = 0; i < 1000; ++i)
   ret = (string)db.ExecuteScalar(dbCommand);
 
dbCommand.Dispose();

There is a real reason ?
What can I change to speed up the second code ?

Thank You!
Apr 2, 2007 at 11:26 AM
There are so many variables that can affect performance that nobody can truly answer such questions. I can't duplicate your exact environment and don't know the accuracy of your performance calculations.

From a theoretical standpoint, I don't think you should see much if any difference using SQL Server. For kicks, I ran similar code to what you are running above to a local instance of SQL Server on my laptop and my results came out the same for both scenarios- around 50ms.

I can't run your identical query, but I wrote one returning the EmailAddress from the Customers Table by CustomerId and measured it with the Stopwatch Class:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("SELECT EmailAddress FROM Customers where CustomerId=@CustomerId");
db.AddInParameter(dbCommand, "CustomerId", DbType.Int32, 1);
 
string emailAddress;
 
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
 
for (int i = 0; i < 1000; ++i)
     emailAddress = (string)db.ExecuteScalar(dbCommand);
 
stopwatch.Stop();
TimeSpan ts = stopwatch.Elapsed;

For more information, I recommend profiling the application as well as the database. Profilers can have issues when code is run in such a tight loop, but they should be able to tell you something.

Regards,

Dave

_______________________

David Hayden
Microsoft MVP C#
Apr 2, 2007 at 1:18 PM
My configuration is composed from only 2 Computers connected in a LAN, Client machine connect, in Integrated Security, to a W2003 Server SP2 with SQL Server 2005 SP2.
I'm using Enterprise Library 3.0 - February 2007 CTP.
The strange behavior is that I ran every snipped code on the same configuration many times and the results are the same! 10sec for the version with parameters and 1sec or less with the no parameter version.

Thank you!