mysql 第一次连接时间很长_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

解决方案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

Alright after doing some testing I found that MySql Connector that devart provides much faster then found on MySql website. Here is the link in case anyone is intrested in testing both. http://www.devart.com/dotconnect/mysql/download.html[^]. There is free version which can be used "dotConnect for MySQL 8.3 Express"

Okay, it is a good thing that you're getting the data from your database even though it is late. That means that your connection doesn't have problem.

The problem might be in your database design or may be in the query. So if it is the first case, then Normalize[^] the database.

And if it is the second, then it simply depends upon the complexity of the query, so optimize that.

-KR