This post falls into the category of "Solution for a technical issue for which I found no help on the Internet".
Overview
If you're using adhoc SqlParameters with a SqlDataAdapter and notice that calls to the SqlDataAdapter's Fill method take longer than expected, try using a typed SqlParameter instead of an adhoc SqlParameter.
Example of an adhoc SqlParameter:
adapter.Parameters.Add("@WMSReceiptID", wmsReceiptID);
Example of a typed SqlParameter:
SqlParameter parameter = adapter.SelectCommand.Parameters.Add("@WMSReceiptID", SqlDbType.VarChar, 64);
parameter.Value = wmsReceiptID;
adapter.Parameters.Add(parameter);
When using an adhoc SqlParameter, the ADO.NET implementation grabs schema information. In this case, it led to SqlAdapter.Fill taking 20 seconds to execute. I switched in the typed SqlParameter and execution speed returned to normal.
The gory details
Today I tested a bug fix on a search screen. The business layer builds a dynamic query based upon the search criteria entered by the user. With shock, I noticed that it was taking 20 seconds to perform the search. The request was carried out across a VPN connection to the customer's network, but it had never taken 20 seconds to execute this query.
At first I thought it was a dormant problem with the SQL that finally decided to wake up. But when executed via SQL Query Analyzer, the query completed in 1 or 2 seconds.
To isolate the problem, I threw the code into a console application. The code uses a SqlDataAdapter to fill a DataTable. The particular instance of this query had one parameter that was filled via an ad hoc SqlParameter.
Following is a stripped down version of the test code...
SqlConnection connection = new SqlConnection(Database.ConnectionString);
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand =
new SqlCommand("SELECT ...", connection);
adapter.SelectCommand.Parameters.Add("@WMSReceiptID", "[SomeRecordID]");
DataTable table = new DataTable();
try
{
adapter.Fill(table);
}
catch (Exception E)
{
Console.WriteLine("Error: " + E.Message);
}
}
finally
{
connection.Close();
}
The test program also took 20 seconds or so to execute the source code line "adapter.Fill(table);". For kicks, I removed the SqlParameter and hardcoded the receipt ID. Bam! The call to adapter.Fill returned in 1 second.
Hmmm. Why would using a SqlParameter cause slow performance? I've used them hundreds, if not thousands of them, with no slowdown.
I downloaded a trial version of Automated QA's AQTime and profiled the test application. AQTime reported a large amount of time in UnsafeNativeMethods/Dbnetlib::ConnectionRead. I used the Call Graph to backtrack. Once I reached method SqlDataReader.get_MetaData. I reasoned that the adhoc SqlParameter was causing ADO.NET to fetch the schema information and doing so was taking a lot of time.
I switched to a typed SqlParameter and performance went back to its expected level.
--
Sean Winstead
Tags: ADO.NET, .NET, SQL Server