SQLDataReader根据条件变慢的原因是什么？

Question

我将使用一个SQLDataReader来填充一个asp.net页面上的GridView (GridView1)。SQLDataReader在C# Codebehind中进行设置，如下所示：

        string MySQLString;
        MySQLString = "SELECT * FROM [vw_Report_Latest_v3_1] WHERE [FKID_Contract]=@Contract";
        if ((string)Session["TSAreaString"] != "") { MySQLString = MySQLString + " AND [L1_Name]=@PA1"; }
        if ((string)Session["TSSiteString"] != "") { MySQLString = MySQLString + " AND [L2_Name]=@PA2"; }
        if ((string)Session["TSFeatureString"] != "") { MySQLString = MySQLString + " AND [L3_Name]=@PA3"; }
        if ((string)Session["TSS1"] != "") { MySQLString = MySQLString + " AND [Spare1]=@S1"; }
        if ((string)Session["TSS2"] != "") { MySQLString = MySQLString + " AND [Spare2]=@S2"; }
        if ((string)Session["TSS3"] != "") { MySQLString = MySQLString + " AND [Spare3]=@S3"; }
        if ((string)Session["TSS4"] != "") { MySQLString = MySQLString + " AND [Spare4]=@S4"; }
        if ((string)Session["TSS5"] != "") { MySQLString = MySQLString + " AND [Spare5]=@S5"; }
        if ((string)Session["TSTaskString"] != "") { MySQLString = MySQLString + " AND [Operation_Name]=@PA4"; }
        if ((string)Session["TSTeamString"] != "") { MySQLString = MySQLString + " AND [Team_Name]=@Team"; }
        //finish
        MySQLString = MySQLString + " ORDER BY [OperationOrder], [L1_Name], [L2_Name], [L3_Name], [Operation_Name], [Team_Name]";
        try
        {
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(MySQLString, Conn);
            Cmd.Parameters.AddWithValue("@Contract", Convert.ToInt32(invCID.Text));
            if ((string)Session["TSAreaString"] != "") { Cmd.Parameters.AddWithValue("@PA1", (string)Session["TSAreaString"]); }
            if ((string)Session["TSSiteString"] != "") { Cmd.Parameters.AddWithValue("@PA2", (string)Session["TSSiteString"]); }
            if ((string)Session["TSFeatureString"] != "") { Cmd.Parameters.AddWithValue("@PA3", (string)Session["TSFeatureString"]); }
            if ((string)Session["TSS1"] != "") { Cmd.Parameters.AddWithValue("@S1", (string)Session["TSS1"]); }
            if ((string)Session["TSS2"] != "") { Cmd.Parameters.AddWithValue("@S2", (string)Session["TSS2"]); }
            if ((string)Session["TSS3"] != "") { Cmd.Parameters.AddWithValue("@S3", (string)Session["TSS3"]); }
            if ((string)Session["TSS4"] != "") { Cmd.Parameters.AddWithValue("@S4", (string)Session["TSS4"]); }
            if ((string)Session["TSS5"] != "") { Cmd.Parameters.AddWithValue("@S5", (string)Session["TSS5"]); }
            if ((string)Session["TSTaskString"] != "") { Cmd.Parameters.AddWithValue("@PA4", (string)Session["TSTaskString"]); }
            if ((string)Session["TSTeamString"] != "") { Cmd.Parameters.AddWithValue("@Team", (string)Session["TSTeamString"]); }
            Cmd.Connection = Conn;
            SqlDataReader reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            GridView1.DataSource = reader;
            GridView1.DataBind();
        }
        finally
        {
            if (Conn != null) { Conn.Close(); }
        }

这给我带来了严重的问题。例如，如果我们通过给 TSAreaString 赋值来设置 L1_Name 为 "Town"，它将显示所有 L1_Name 为 "Town" 的内容。这很好。因为 Town 很大，所以需要几秒钟的时间。

然而，如果我们把 L1_Name 设置为 "Town" 并且在此示例中将 TSS3 设置为 "County"，那么它就需要更长的时间 - 有时甚至要超过一分钟 - 尽管它检索相同数量的记录或者有时是更少。

不幸的是，由于客户的规定，我们必须这样做 - 我们不能只搜索 "Town"，我们必须搜索 "Town" 和 "County"。

这个视图 vw_Report_Latest_v3_1 运行得非常好。即使使用上述条件也没有任何问题。在 SQL Server 2008 中，仅通过视图的两种情况 - Town 和 Town AND County - 所需的时间相同。

我很确定这与读取/绑定有关。

- user25730

---

你的代码让我头疼。你应该使用using语句而不是try/finally。你还应该将所有相关参数存储在一个对象中，然后将该对象存储在Session中，而不是将大量单独的对象存储在Session中。最后，你不应该在代码后台进行数据库工作，你应该有一个单独的层来负责这个任务。 - mason

如果这是数据绑定问题，您可以测量执行查询的时间和执行数据绑定的时间... 但这绝对不是数据绑定问题... 一旦数据被填充，无论获取数据的查询如何，只要返回相同数量的数据就不重要了。 - Laurent Lequenne

在SQL管理工具中运行生成的查询并查看执行计划。 - Magnus

必须承认我对这种SQL还很陌生。不过，我不认为SQL是答案——通过SQL Management Studio，即使我输入尽可能多的条件，视图也能正常工作，并且通过上述Codebehind也基本正常运行。只是在Codebehind版本中添加条件会出现某些原因导致速度变慢。 - user25730

可能是AddWithValue()的问题。有时候ADO.Net会猜测错误的参数类型，导致索引使用出现问题。 - Joel Coehoorn

尝试过仅使用Add()（先指定类型，然后添加参数值）而不是AddWithValue() - 但没有成功。虽然是个好主意。 - user25730

Answer 1

有几个方面需要改进。

• 您应该使用StringBuilder代替string = string +""; 它更高效
• 建议使用!string.IsNullOrEmpty((string)Session[""])而不是!= ""，这样可以捕获null、string.empty和""
• (个人偏好) 不要害怕空格。if语句放在同一行很难读。
• 很好地将内容包装在{}中，如果不这样做，下一个人会很困难
• 分离层是一个好主意(正如@mason所提到的)。我个人有一个数据层来存储查询。没有逻辑。然后是业务层或类层。它包含逻辑。清理、验证等... 然后是代码后面，将值传递到类层。
• 我看到两种类型的业务层。有真正的对象风格。数据层转换为datatable，然后加载到类中。查找POCO以了解其思想。另一种是我几年前开始的层系统。每个方法都是自包含的，并将东西传递给数据层，如果是选择，则返回数据表。
• 提取与数据库联系的代码。(参见底部的代码)

你提到了一个视图，请检查你的索引。由于这是MySQL...我不知道如何操作，但在使用Microsoft SQL时，可以使用所谓的估计执行路径。因此，您可以将select语句放入MSSMS软件中，而不是执行，然后单击“显示估计执行路径”按钮，该按钮将为索引等提供建议。

这是数据层的样子以及它如何使用连接器（下一个代码块）。

private MySQLConnector _MySQL = null;
protected MySQLConnector MySQL
{
   get
   {
      if (_MySQL == null)
      {
         _MySQL = new MySQLConnector();
      }
      return _MySQL;
   }
}

public void Update(int programId, int LocationId, string Name, string modifiedBy)
   {
   List<MySqlParameter> parameterList = new List<MySqlParameter>();

   parameterList.Add(new MySqlParameter("ProgramID", programId));
   parameterList.Add(new MySqlParameter("LocationId", LocationId));
   parameterList.Add(new MySqlParameter("Name", Name));
   if (!string.IsNullOrEmpty(modifiedBy))
   {
      parameterList.Add(new MySqlParameter("ModifiedBy", modifiedBy));
   }
   else
   {
      parameterList.Add(new MySqlParameter("ModifiedBy", DBNull.Value));
   }

   const string TheSql = @"
            UPDATE ProgramLocation
            SET
           Name = @Name,
               ModifiedOn = GETDATE(),
               ModifiedBy = @ModifiedBy
            WHERE
        ProgramID = @ProgramID
        AND LocationId = @LocationId";

   MySQL.ExecuteNonQuerySql(TheSql, parameterList);
}

这是连接数据库的代码。它有点过时，你可能需要根据使用的包来更改连接MySQL数据库的方式。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Reflection;
using MySql.Data.MySqlClient;

namespace DEFINETHENameSpace
{
    public class MySQLConnector
    {
        private string connString = null;

        public string TheConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(connString))
                {
                    //  connString = ConfigurationManager.ConnectionStrings["MySQLConnection"].ConnectionString; 
                    throw new Exception("No Connection String Specified");
                }

                return connString;
            }

            set
            {
                connString = value;
            }
        }

        private Exception errorMessage;

        public Exception ErrorMessage
        {
            get
            {
                return errorMessage;
            }

            set
            {
                errorMessage = value;
            }
        }

        #region ExecuteNonQuery
        /// <summary>
        /// THis will execute a non query, such as an insert statement
        /// </summary>
        /// <returns>1 for success, 0 for failed.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public int ExecuteNonQuery(string theSQLStatement)
        {
            int returnValue = 0;

            if (!string.IsNullOrEmpty(theSQLStatement))
            {
                MySqlConnection connection = new MySqlConnection(TheConnectionString);
                MySqlCommand command = connection.CreateCommand();

                try
                {
                    command.CommandText = theSQLStatement;
                    connection.Open();
                    command.ExecuteNonQuery();

                    //Success
                    returnValue = 1;
                }
                catch (Exception ex)
                {
                    returnValue = 0;
                    throw ex; //ErrorMessage = ex; 
                    // WriteToLog.Execute(ex.Message, EventLogEntryType.Error);
                }
                finally
                {
                    command.Dispose();
                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                    }

                    connection.Dispose();
                }
            }

            return returnValue;
        }

        /// <summary>
        /// THis will execute a non query, such as an insert statement
        /// </summary>
        /// <returns>1 for success, 0 for failed.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public int ExecuteNonQuery(string theSQLStatement, List<MySqlParameter> parameters)
        {
            if ((parameters != null) && (parameters.Count > 0))
            {
                return ExecuteNonQuery(theSQLStatement, parameters.ToArray());
            }
            else
            {
                return ExecuteNonQuery(theSQLStatement);
            }
        }

        /// <summary>
        /// THis will execute a non query, such as an insert statement
        /// </summary>
        /// <returns>1 for success, 0 for failed.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public int ExecuteNonQuery(string theSQLStatement, MySqlParameter[] parameters)
        {
            if ((parameters == null) || (parameters.Count() <= 0))
            {
                return ExecuteNonQuery(theSQLStatement);
            }

            int returnValue = 0;

            if (!string.IsNullOrEmpty(theSQLStatement))
            {
                MySqlConnection connection = new MySqlConnection(TheConnectionString);
                MySqlCommand command = connection.CreateCommand();

                try
                {
                    command.CommandText = theSQLStatement;
                    command.Parameters.AddRange(parameters);
                    connection.Open();
                    command.ExecuteNonQuery();

                    //Success
                    returnValue = 1;
                }
                catch (Exception ex)
                {
                    returnValue = 0;
                    throw ex; //ErrorMessage = ex; 
                    //WriteToLog.Execute(ex.Message, EventLogEntryType.Error);
                }
                finally
                {
                    command.Dispose();
                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                    }

                    connection.Dispose();
                }
            }

            return returnValue;
        }

        #endregion

        #region Execute
        /// <summary>
        /// THis will execute a query, such as an select statement
        /// </summary>
        /// <returns>Populated Datatable based on the sql select command.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public DataTable Execute(string theSQLStatement)
        {
            DataTable resultingDataTable = new DataTable();

            if (!string.IsNullOrEmpty(theSQLStatement))
            {
                MySqlConnection connection = new MySqlConnection(TheConnectionString);
                MySqlCommand command = connection.CreateCommand();

                try
                {
                    command.CommandText = theSQLStatement;
                    connection.Open();

                    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command.CommandText, connection);
                    dataAdapter.Fill(resultingDataTable);

                    //Success
                }
                catch (Exception ex)
                {
                    throw ex; //ErrorMessage = ex; 

                    //WriteToLog.Execute(ex.Message, EventLogEntryType.Error);
                }
                finally
                {
                    command.Dispose();
                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                    }

                    connection.Dispose();
                }
            }

            return resultingDataTable;
        }

        /// <summary>
        /// THis will execute a query, such as an select statement
        /// </summary>
        /// <returns>Populated Datatable based on the sql select command.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public DataTable Execute(string theSQLStatement, List<MySqlParameter> parameters)
        {

            if ((parameters != null) && (parameters.Count > 0))
            {
                return Execute(theSQLStatement, parameters.ToArray());
            }
            else
            {
                return Execute(theSQLStatement);
            }
        }

        /// <summary>
        /// THis will execute a query, such as an select statement
        /// </summary>
        /// <returns>Populated Datatable based on the sql select command.</returns>
        /// <author>James 'Gates' R.</author>
        /// <createdate>8/20/2012</createdate>
        public DataTable Execute(string theSQLStatement, MySqlParameter[] parameters)
        {
            if ((parameters == null) || (parameters.Count() <= 0))
            {
                return Execute(theSQLStatement);
            }

            DataTable resultingDataTable = new DataTable();

            if (!string.IsNullOrEmpty(theSQLStatement))
            {
                MySqlConnection connection = new MySqlConnection(TheConnectionString);
                MySqlCommand command = connection.CreateCommand();

                try
                {
                    command.CommandText = theSQLStatement;
                    connection.Open();

                    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command.CommandText, connection);
                    dataAdapter.SelectCommand.Parameters.AddRange(parameters);
                    dataAdapter.Fill(resultingDataTable);

                    //Success
                }
                catch (Exception ex)
                {
                    throw ex; //ErrorMessage = ex; 
                    //WriteToLog.Execute(ex.Message, EventLogEntryType.Error);
                }
                finally
                {
                    command.Dispose();
                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                    }

                    connection.Dispose();
                }
            }

            return resultingDataTable;
        }
    }
        #endregion
}

- James Gates R.

---

在这种情况下，使用 StringBuilder 不会带来明显的差异。 - Joel Coehoorn

Joel是正确的，已经测试了很多次。为了防止无效转换（以防万一），我建议使用!string.IsNullOrEmpty(Session[""] as string)。 - Erik Philips

好观点。"as string"比(string)更好。^_^ - James Gates R.

Answer 2

你的SQL连接代码似乎不是问题所在，GridView也不是。尝试将你的Session变量保存到一个Dictionary<string, object>或Dictionary<string, string>中（或类似的方法），在需要时从中检索值，而不是每次都访问浏览器的Session（两次）。

另外一种可能是视图/底层表的索引不存在或有问题。确保也要检查这些内容。

一些需要考虑的事情： 1.针对你所要做的事情，制作一个存储过程，不要手动构造SQL查询语句。当你增加复杂性时，会给自己带来麻烦。例如，不是：

if ((string)Session["TSAreaString"] != "") { MySQLString = MySQLString + " AND [L1_Name]=@PA1"; }

你的存储过程可以很容易地使用COALESCE或ISNULL来实现相同的结果。

--parameter for stored procedure
@TSAreaString nvarchar(max) = NULL


SELECT v.* 
FROM View v
WHERE v.TSAreaString = COALESCE(@TSAreaString, v.TSAreaString)

（SQL Server 语法）

如果您采用这种方法，可以消除代码的前一半，并在后一半执行以下操作：

Cmd.Parameters.AddWithValue("@Team", String.IsNullOrWhiteSpace((string)Session["TSTeamString"]) ? DBNull.Value : (string)Session["TSTeamString"]; 

然而，如果您要继续使用相同的方法：

1. 请使用StringBuilder而不是string。它是不可变的，如果您担心性能，由于这一点，它将表现更好。

2. 将您的连接对象和命令对象包装到一个using子句中，以便自动处理暂时的处置（长期来看，请创建自己的类来处理不同的数据库操作）。

3. 实际上，这可能是您问题的一部分，但我不确定，因为我以前没有按您的方式做过。 不要将reader对象作为GridView的数据源，而是创建一个表示数据的类，并使用读取器将其填充为List<YourClass>，其中使用

   while (reader.Read()) { YourList.Add(RetrieveYourClass(reader)); }

（^由于某种原因SO没有对此进行代码突出显示）

- Donald.Record

---

我肯定知道我的代码不是最好的，但现在我倾向于索引（以及我缺乏索引）。我认为我需要知道如何有效地对表进行索引。 - user25730

Answer 3

已经搞定了，对于可能遇到类似问题的人。

基本上，就像其他人所说的那样，其中一部分是 - 不要过多地引用会话状态。相反，我做了以前做过的事情，因为我需要在页面上的标签中使用我的会话，所以只需从页面上的标签中读取，而不是一直去和会话打交道。

然而，主要部分是改变 SQL - 但只是稍微改动了一下。与其使用：

MySQLString = MySQLString + " AND [Spare1]=@S1";

我使用了：

MySQLString = MySQLString + " AND ([Spare1] LIKE @S1)";

看起来封装每个条件并使用 LIKE 是关键 - 现在在所有情况下都运行非常快。

- user25730