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.