SQL查询运行罚款SSMS运行在ASP.NET很慢 [英] SQL Query that runs fine in SSMS runs very slow in ASP.NET

问题描述

我有一个asp.net页面加载，我们知道什么时候从SQL Server Management Studio中执行一切正常（1〜2秒之内）查询，但是当从ASP.NET中的一个SqlCommand执行的查询需要相当长的时间，我似乎无法弄清楚发生了什么以外一行已经被添加到该查询，因为这个问题开始，但是我无法找到问题是什么。

加入了code的那一行是行6： bi.INGR_ code = 0

！

SQL语句

  SELECT bh.JOB_NUMBER，j.DESCRIPTION，SUM（bi.INGR_ACTUAL）以总
    从BATCH_HEADER为：BH LEFT OUTER JOIN
        BATCH_INGR为Bi
            ON bh.BATCH_ID = bi.BATCH_ID和
            bh.FACTORY = bi.FACTORY和
            bi.INGR_ code＆LT;＆GT; 0 LEFT OUTER JOIN
    ServerNameReplaced.man_prod.dbo.JOBS为J
        ON bh.JOB_NUMBER = j.JOB_NUMBER COLLATE database_default和
           bh.FACTORY = j.FACTORY COLLATE database_default
    WHERE（bh.FACTORY = @Factory）和
            （bh.DATETIME_DUMP＆GT; = @StartDate）和
            （bh.DATETIME_DUMP＆LT; @EndDate）
    GROUP BY bh.JOB_NUMBER，j.DESCRIPTION
    ORDER BY bh.JOB_NUMBER
 

ASP.NET code文件的背后

  //临时列表
名单＆LT; BatchItem＆GT;数据=新的名单，其中，BatchItem＆GT;（）;

字符串的SqlCommand = DBHelper.LoadSQLStatement（batchdescription.sql）;

System.Data.SqlClient.SqlConnection的SqlConnection =新System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringNameReplaced"].ConnectionString);
System.Data.SqlClient.SqlCommand的SqlCommand =新System.Data.SqlClient.SqlCommand（的SqlCommand，SqlConnection的）;

尝试
{
    sqlCommand.Parameters.Add（@StartDate，System.Data.SqlDbType.DateTime）。价值=起始日期;
    sqlCommand.Parameters.Add（@EndDate，System.Data.SqlDbType.DateTime）。价值=结束日期;
    sqlCommand.Parameters.Add（@Factory，System.Data.SqlDbType.VarChar，2）。价值=厂;

    在SQLConnection.open（）;

    SqlDataReader对象的DataReader = sqlCommand.ExecuteReader（）;

    而（DataReader.Read（））
    {
        data.Add（
            新BatchItem（）
            {
                jobnumber可以= DataReader的[0]的ToString（）
                说明= DataReader的[1]的ToString（）
                总= decimal.Parse（DataReader的[2]的ToString（））
            }）;
    }
}
赶上（例外前）
{
    //处理异常
}
最后
{
    在SQLConnection.close（）;
}
 

解决方案

有各种各样的事情，可以继续。

首先，伊万湾是正确的连接参数和SET选项可能是SSMS和你的ASP.NET客户端之间的不同。这是什么值得探讨的探查，如果你访问它。

其次，如果你在一排在SSMS中运行查询多次，它可能对结果进行高速缓存，这就是为什么它运行得这么快在SSMS中。如果它运行速度慢你第一次打开SSMS并尝试运行它，但随后加速，这是一个标志存在的缓存怎么回事。

至于为什么增加一个额外的条款为连接可能会放慢改革的步伐，这是很难不知道更多关于你的表说为什么，但它不是不可能的，可能都做到了。有没有办法，包括厂指数超过 BATCH_INGR 和 INGR_ code ？你可能现在需要，你包括 INGR_ code 在你的加入条件之一。

要找出最好的办法是看查询计划，有和没有 INGR_ code 条款，看看它是如何不同。是成本数字一个查询比对其他大？是否有表扫描，那里有没有过吗？有一个索引查找变成了一个索引扫描？

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();
}

解决方案

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?

这篇关于SQL查询运行罚款SSMS运行在ASP.NET很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！