SQLDataReader going too slow depending on criteria

Your SQL connection code doesn't look like it is the problem, nor the GridView. Try [on a higher scope] taking your Session variables and saving it into a Dictionary<string, object> or a Dictionary<string, string> (or something of that approach) and retrieve your values from that instead of hitting the browser's session for every value (twice).

Either that or the indexing for your view/underlying tables are non-existent or troublesome. Make sure to check those as well.

Some things to consider: 1. For what you're trying to do, make a stored procedure that doesn't involve you having to construct a SQL query yourself. You will give yourself headaches when you add complexity in the future. For example, instead of:

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

...your stored procedure could very easily use COALESCE or ISNULL to achieve the same results.

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


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

(SQL Server syntax)

If you used this approach, you could eliminate the top half of your code and do something like this in the second half:

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

However, if you going to continue using your same approach:

1. Use StringBuilder instead of string. It is immutable and if you're worried about performance, it will perform better because of this.

2. Wrap your connection object and your command object into a using clause for automatic disposal for the time being (long term, make your own classes to handle the different database operations).

3. And actually this might be part of your problem, but I'm not sure because I haven't done it your way before. Instead of making the reader object the DataSource of the grid view, make a class that represents your data, and use the reader to populate a List<YourClass> of it using

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

(^ SO is not code highlighting this for some reason)

there are several areas of improvement that you could do.

• You should use StringBuilder instead of string = string +""; its more efficient
• instead of != "", i would suggestion !string.IsNullOrEmpty((string)Session[""]). that way it catches null, string.empty, and ""
• (personal preference) Don't be afraid of white space. having the if statements on the same line..its hard to read.
• good job wrapping stuff in {} its a nightmare for the next person if you dont do that
• Its a good idea to seperate layers (as @mason mentioned). I personally have a data layer to house the queries. has no logic. then a business layer or class layer. it houses the logic. cleaning, verifying, etc... then the code behind which passes the values into class layer.
• I see two types of business layers. there is the true object style. data layer turns datatable then it gets loaded into the class. Look up POCO to get an idea of that. the other, which is where I started a few years ago, is a layer system. each method is self contained and just passes stuff to the data layer and if it is a select, then returns a data table.
• extract out the code that contacts the database. (See my code at the bottom)

you mentioned a view. check your indexing. since this is mysql...i dont know hwo to do this but when using Microsoft SQL, you can use whats called estimated execution path. so you would put the select statement in MSSMS software, and instead of executing, you can click the "Display estimated execution path" button which will make suggestions for indexing etc.

here is how your data layer could look and how it could use the connector (which is the next code block)

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

here is the code that contacts the database. its a bit dated and you might need to change it the package your using to contact the MySQL Database.

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
}