c# - SQL Query that runs fine in SSMS runs very slow in ASP.NET

Question

I have an asp.net page that loads a query which we know to run fine (within 1 to 2 seconds) when executed from SQL Server Management Studio, however when executed from ASP.NET in a SQLCommand the query takes substantially longer, I cannot seem to figure out what is happening other than one line has been added to the query since the problem started, however I cannot locate what the issue is.

The offending line of code that was added is line 6: bi.INGR_CODE != 0

SQL Statement

    SELECT  bh.JOB_NUMBER, j.DESCRIPTION, SUM(bi.INGR_ACTUAL) AS TOTAL
    FROM    BATCH_HEADER AS bh LEFT OUTER JOIN 
        BATCH_INGR AS bi
            ON bh.BATCH_ID = bi.BATCH_ID AND
            bh.FACTORY = bi.FACTORY AND
            bi.INGR_CODE <> 0 LEFT OUTER JOIN
    ServerNameReplaced.man_prod.dbo.JOBS AS j
        ON bh.JOB_NUMBER = j.JOB_NUMBER COLLATE database_default AND
           bh.FACTORY = j.FACTORY COLLATE database_default
    WHERE   ( bh.FACTORY = @Factory ) AND
            ( bh.DATETIME_DUMP >= @StartDate ) AND
            ( bh.DATETIME_DUMP < @EndDate )
    GROUP BY bh.JOB_NUMBER, j.DESCRIPTION
    ORDER BY bh.JOB_NUMBER

ASP.NET Code Behind File

//Temporary List
List<BatchItem> data = new List<BatchItem>();

string SQLCommand = DBHelper.LoadSQLStatement( "batchdescription.sql" );

System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringNameReplaced"].ConnectionString);
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(SQLCommand, sqlConnection );

try
{
    sqlCommand.Parameters.Add( "@StartDate", System.Data.SqlDbType.DateTime ).Value = StartDate;
    sqlCommand.Parameters.Add( "@EndDate ", System.Data.SqlDbType.DateTime ).Value = EndDate;
    sqlCommand.Parameters.Add( "@Factory", System.Data.SqlDbType.VarChar, 2 ).Value = Factory;

    sqlConnection.Open();

    SqlDataReader DataReader = sqlCommand.ExecuteReader();

    while ( DataReader.Read() )
    {
        data.Add(
            new BatchItem()
            {
                JobNumber = DataReader[0].ToString(),
                Description = DataReader[1].ToString(),
                Total = decimal.Parse( DataReader[2].ToString() )
            } );
    }
}
catch ( Exception ex )
{
    //handle exceptions
}
finally
{
    sqlConnection.Close();
}

Answer 1

There are all kinds of things that could be going on.

First, Ivan G. is right that connection parameters and SET options might be different between SSMS and your ASP.NET client. That's something worth looking into in Profiler, if you have access to it.

Second, if you've run your query multiple times in a row in SSMS, it's possible the results are being cached and that's why it runs so fast in SSMS. If it runs slowly the first time you open up SSMS and try to run it, but then speeds up, that's a sign there's caching going on.

As for why adding one extra clause to a join could slow things down, it's hard to say why without knowing more about your tables, but it's not impossible that that could have done it. Is there an index over BATCH_INGR that includes both FACTORY and INGR_CODE? You might need one now that you're including INGR_CODE in your join conditions.

The best way to find out is to look at the query plan with and without the INGR_CODE clause and see how it differs. Is the cost figure for one query bigger than for the other? Are there table scans where there weren't before? Has an index seek turned into an index scan?