CODEWITHSUNDEEP
c#asp.netsqlstored-procedures
jaekie
jaekie

Reputation: 2303

Unusual SQL/Data issues

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)

Upvotes: 0

Views: 158

Answers (2)

to StackOverflow
to StackOverflow

Reputation: 124686

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.

Upvotes: 2

Yahia
Yahia

Reputation: 70369

The information you give doesn't contain enough details to really help...

IF SSMS is really that much faster then the reason could be some session/connection setting - SSMS uses subtly different settings in comparison to .NET.

For some explanation and hints on what could be different/wrong etc. see http://www.sommarskog.se/query-plan-mysteries.html

Upvotes: 0

Related Questions