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();
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…