Multiple stored procedure calls or loop through an array?

Question

I am currently setting the tooltips on a report grid based on values stored in a table. I do this because I have a LOT of grids and a lot of tooltips and this makes it easy to manage them all from one place without updating source code.

My question. Is it faster to load the tooltips in this fashion or to load them by loading all of tooltips at once and looping through an array?

It seems that one SP call for all of the tooltips would be faster than 10-20. Is this assumption correct? If so, can I see an example of how you'd do this in an array or list?

            sqlconn.Open();
        SqlCommand com = new SqlCommand("sp_ToolTipLookup", sqlconn) { CommandType = System.Data.CommandType.StoredProcedure };
        SqlParameter pFieldName = new SqlParameter("@FieldName", "");
        for (int i = 0; i < rptgrid.Columns.Count; i++) 
        {
            pFieldName.Value = rptgrid.Columns[i].ToString();
            com.Parameters.Add(pFieldName); //adding the field name to the SP
            SqlDataReader data = com.ExecuteReader(); //Open the SP
            if (data.Read()) rptgrid.Columns[i].ToolTip = data["ToolTip"].ToString(); //If there is a resulting Tooltip, apply it to the grid
            data.Close();
            com.Parameters.Remove(pFieldName);
        }
        sqlconn.Close();

An example using a list would be more like this (and if this is faster, I could potentially load the list once per session and just store it in memory).

sqlconn.Open();
        SqlCommand com = new SqlCommand("Select * from ToolTips", sqlconn) { CommandType = System.Data.CommandType.Text };
        SqlDataReader data = com.ExecuteReader();
        List<ToolTip> tips = new List<ToolTip>();
        while (data.Read())
        {
            tips.Add(new ToolTip { fieldname = data["FieldName"].ToString(), tooltip = data["ToolTip"].ToString() } );
        }



        for (int i = 0; i < rptgrid.Columns.Count; i++) //Changed to visible column to speed it up a bit.
        {
            for (int x = 0; x < tips.Count; x++)
            {
                if (rptgrid.Columns[i].Name == tips[x].fieldname)
                {
                    rptgrid.Columns[i].ToolTip = tips[x].tooltip;
                }
            }
        }
        data.Close();
        sqlconn.Close();

Answer 1

The stored proc sp_ToolTipLookup must return at least the data ToolTip and FieldName, but you have to remove the filter about the tool tip name in the where clause..

string connectionString = ... //web|app.config

using (SqlConnection sqlconn = new SqlConnection(connectionString)){
   using(SqlCommand com = new SqlCommand("sp_ToolTipLookup", sqlconn)){

       com.CommandType = System.Data.CommandType.StoredProcedure 

       sqlconn.Open();
       using (SqlDataReader data = com.ExecuteReader()){ //Call the SP

           while(data.Read()) {
               foreach(var col in rptgrid.VisibleColumns){
                   if (col.Name == data["FieldName"].ToString()){
                       rptgrid.VisibleColumns[col.Index].ToolTip = data["ToolTip"].ToString();
                   }
               }
           }
       }
   }
}

Answer 2

Since the Tooltips are likely not going to change while the application is running, I would recommend actually loading your tooltips into your application as a public static property of your Main or Program or whatever your root class is for your app. This would make tooltips available to the entire application and avoid different parts of the apps having to make different database calls to get their tooltips. I'd also put a time checker in the property Get method so that every few hours the data is refreshed.

Answer 3

Correct one SP call loading all tooltips would be faster provided that the SP is designed efficiently.

Answer 4

Ok so to summarize: The answer is a combination of three received so far.

One SP to load all ToolTips

Do this once and make them available to the application.

Thanks guys. I wish I could select more than one correct answer.