Slow database queries

[MattNorman]

For some reason I am having some issues with slowness when retrieving a very small amount of data from SQL Server.

I have amended the query to run in a new thread however it still takes a few seconds for around 20 rows with around 5 columns each to load.

This might just be the connection as I am testing this across VPN but just wanted to check if anything I am doing on the C# side may not be optimal:

public static List<AgentGroupModel> GetAllGroups()
{
    string methodName = "GetAllGroups()";
    List<AgentGroupModel> returnData = new List<AgentGroupModel>();
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

    try
    {
        builder = DataHelper.GetConnStringBuilder();

        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();
            SqlCommand cmd;
            SqlDataReader dr;

            //Get user permissions.
            cmd = new SqlCommand("SELECT * FROM ApexCCM.dbo.AgentGroups ORDER BY GroupName", connection);
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                AgentGroupModel groupData = new AgentGroupModel();
                groupData.GroupID = int.Parse(dr["GroupID"].ToString());
                groupData.GroupName = dr["GroupName"].ToString();
                groupData.AgentsAssigned = DataManagerAgentGroupMapping.GetAgentMappingCount(groupData.GroupID);
                groupData.SkillsAssigned = DataManagerSkillGroupMapping.GetSkillMappingCount(groupData.GroupID);
                returnData.Add(groupData);
            }
            dr.Close();
        }

        builder.Clear();
        return returnData;
    }
    catch (Exception ex)
    {
        builder.Clear();
        DataManagerLogs.WriteToErrorLog($"{classNamespace}.{methodName}", ex.ToString(), "");
        AppDataStore.MainViewModelRef.MessageList.Add(new ExceptionModel { ClassName = $"{classNamespace}.{methodName}", Title = "Error", Message = ex.Message });
        return returnData;
    }
}

 

[Skydiver]

What does the profiler say about where the bottleneck is at?

If you are getting only 5 columns, explicitly list them in your SELECT statement instead of using SELECT *.

 

[jmcilhinney]

What happens if you just load the data reader into a DataTable? I wonder whether your processing is having a detrimental impact. I'm not sure that it would make a huge difference but this is a bit crazy:

int.Parse(dr["GroupID"].ToString())

If the value represents an int then why isn't it being stored as such and, if it is being stored as such, why are you converting it to a string and then back again? Don't store or convert data to text that isn't text unless you need it for display or the like. If you have int data then store it as such and retrieve it as such:

dr.GetInt32(dr.GetOrdinal("GroupID"))