Unusual SQL/Data issues

Question

We have a report that has been giving us some serious issues, so I decided to put it into a console application in order to troubleshoot the issues.

The report is just a simple single select from SQL, returning approximately 25 columns, and our date range can be 3-6 months, returning around 10k rows, so we are not talking about a lot of data.

Here is whats happening, when the report runs, it is timing out from our website, in the console, it takes anywhere from 13-18 mins to finish, the wait seems to happen at the

da.Fill(ds);

Now here is the strange thing, it runs approximately 1-3 seconds within SQL Server Management Studio, and when our Delphi developers create a similar application, it is also a few seconds to run, this only happens using .NET

We tried changing from a dataset to loading into a datareader, using this code..

using (var dr = _command.ExecuteReader())
{
  if (dr.HasRows)
  {
    int i = 0;
    while (dr.Read())
    {
      var startRead = DateTime.Now;
      Console.Write("{2}\t{0}\t{1}\t", dr.GetInt32(0), dr.GetString(1), i);
      var tookRead = DateTime.Now.Subtract(startRead);
      Console.WriteLine("Took: " + tookRead);
      i++;
    }
}

However it did not help at all, it just displays in chucks but has frequent delays. I'm thinking its SQL, but can't explain why it works fine in Delphi and in SQL Management Studio.

I've tried using .NET 2.0, 3.5 and 4, happens on all frameworks.

Here is my code

public static DataSet GetData()
{
  var now = DateTime.Now;
  var _command = new SqlCommand();
  var _connection = new SqlConnection();

  try
  {
    _connection.ConnectionString = connectionString;

    _command.Connection = _connection;
    _command.CommandText = storedProcedure;
    _command.CommandType = CommandType.StoredProcedure;
    _command.CommandTimeout = 60;

    if (string.IsNullOrEmpty(_connection.ConnectionString)) { throw new Exception("Connection String was not supplied"); }

    _command.Parameters.Add(new SqlParameter("DateFrom", dateFrom));
    _command.Parameters.Add(new SqlParameter("DateTo", dateTo));

    SqlDataAdapter da;
    var ds = new DataSet();

    _connection.Open();

    var done = DateTime.Now;

    da = new SqlDataAdapter(_command);
    da.Fill(ds);

    if (ds == null) { throw new Exception("DataSet is null."); }
    if (ds.Tables.Count == 0) { throw new Exception("Table count is 0"); }

    var took = done.Subtract(now);

    return ds;

  }
  catch (Exception ex)
  {
    File.WriteAllText(Path.Combine(Application.StartupPath, String.Format("Exception{0:MMddyyyy_HHmmss}.log", DateTime.Now)), ex.ToString());
  }
  finally
  {
    if (_connection.State != ConnectionState.Closed) { _connection.Close(); }
  }

  return null;
}

Any ideas? Our DBA is blaming the framework, I'm actually blaming something in SQL.. (maybe statistics, or corrupted db)

How do you think it is SQL if the query runs just fine from within SSMS? — JNK
have scrolled down to the last row in SQL Management studion after executing the SQL statement ? — Yahia
Have a look at this thread: stackoverflow.com/questions/250713/… — granaker
just as a side note, I think the var done = DateTime.Now; line should be moved in the finally clause. Right now it fails to time the most important thing - the da.Fill(ds); line — Cristian Lupascu

Joe Joe · Accepted Answer · 2012-01-16T20:09:03

Differences in SQL performance between .NET and other clients (SQL Management Studio) are usually down to the connections being configured differently - frequent culprits are ANSI_NULLS; ANSI_PADDING.

Try looking at how the connection is configured in SQL Management Studio, then replicate the same thing in your .NET application.

Unusual SQL/Data issues

2 Answers