MySQL query takes long time at first execution.

See more:

C#

MySQL

Hi, This method when executed for the first time takes around 1250 mili seconds but if I execute same method without closing application the time drops to only 300 mili seconds.

public void GetEmployeeRightsParameters()
        {
            try
            {
                MySqlDataAdapter da = null;
                MySqlConnection Connection = null;
                MySqlTransaction Transaction = null;
                MySqlCommand Command = null;
                Connection = new MySqlConnection(connectionString);

                DataSet ds = null;

                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                Command = new MySqlCommand("GetEmployeeRights", Connection);
                Command.CommandType = CommandType.StoredProcedure;

                //Add Parameters is procedures requires.
                Command.Parameters.Add(new MySqlParameter("p_EmployeeID", 1));

                if (Transaction != null)
                {
                    Command.Transaction = Transaction;
                }

                da = new MySqlDataAdapter();
                da.SelectCommand = Command;

                DataTable dt = new DataTable();

                Stopwatch sw = new Stopwatch();
                sw.Start();
                da.Fill(dt);
                sw.Stop();
                //Writing Execution Time in label 
                string ExecutionTimeTaken = string.Format("Minutes :{0}\nSeconds :{1}\n Mili seconds :{2}", sw.Elapsed.Minutes, sw.Elapsed.Seconds, sw.Elapsed.TotalMilliseconds);

                MessageBox.Show(ExecutionTimeTaken);
            }
            catch
            {
                throw;
            }
        }

This method executes in same time around 300 mili seconds. Only difference I see is the use of MySqlCommand so my question is that would it be good practice to give Adapter a plain query?

public void GetEmployeeRightsPlain()
       {
           MySqlConnection connection = new MySqlConnection(connectionString);
           connection.Open();

           DataSet dataset = new DataSet();
           MySqlDataAdapter adapter = new MySqlDataAdapter("CALL GetEmployeeRights(1)", connection);

           Stopwatch sw = new Stopwatch();
           sw.Start();
           adapter.Fill(dataset);
           sw.Stop();
           //Writing Execution Time in label
           string ExecutionTimeTaken = string.Format("Minutes :{0}\nSeconds :{1}\n Mili seconds :{2}", sw.Elapsed.Minutes, sw.Elapsed.Seconds, sw.Elapsed.TotalMilliseconds);

           MessageBox.Show(ExecutionTimeTaken);
       }

There are only 70 records returned against this procedure.

DELIMITER $$

CREATE DEFINER=`cellsmar`@`%` PROCEDURE `GetEmployeeRights`(p_EmployeeID int)
BEGIN

DECLARE l_EmployeeID int;
SET l_EmployeeID = p_EmployeeID;

IF(l_EmployeeID = 0)
THEN
	SELECT FormID,FormName,CanAccess,Type from RightsCatalog; 
ELSE
	SELECT
		 ER.StoreID
		,ER.FormID
		,FormName
		,IFNULL(ER.CanAccess,1) as CanAccess
		,RC.Type 
		FROM EmployeeRight ER 
		RIGHT OUTER JOIN RightsCatalog RC 
		ON RC.FormID = ER.FormID  
		WHERE EmployeeID = l_EmployeeID;
END IF;
-- ****** [Objec...  ts ] Script Date: 02/24/2014 10:00:33 *******

END