MySQL查询首次执行需要很长时间。 [英] MySQL query takes long time at first execution.

问题描述

这个方法第一次执行大约需要1250毫秒，但是如果我在没有关闭应用程序的情况下执行相同的方法，那么时间将减少到300毫秒。

  public   void  GetEmployeeRightsParameters（）
 {
 尝试 
 {
 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; 
 
  //  添加参数是程序要求。 
命令。 Parameters.Add（ new  MySqlParameter（  p_EmployeeID， 1 ））; 
 
  if （Transaction！=  null ）
 {
 Command.Transaction = Transaction; 
} 
 
 da =  new  MySqlDataAdapter（）; 
 da.SelectCommand = Command; 
 
 DataTable dt =  new  DataTable（）; 
 
秒表sw = 新秒表（）; 
 sw.Start（）; 
 da.Fill（dt）; 
 sw.Stop（）; 
  //  在标签中写入执行时间 
  string  ExecutionTimeTaken =  string  .Format（ 分钟：{0} \ n秒：{1} \ n Mili秒：{2}，sw.Elapsed.Minutes，sw.Elapsed.Seconds，sw.Elapsed.TotalMilliseconds）; 
 
 MessageBox.Show（ExecutionTimeTaken）; 
} 
  catch  
 {
  throw ; 
} 
} 

此方法同时执行约300 mili秒。我看到的唯一区别是MySqlCommand的使用，所以我的问题是给适配器一个简单的查询是好的做法吗？

  public   void  GetEmployeeRightsPlain（）
 {
 MySqlConnection connection =  new  MySqlConnection（connectionString）; 
 connection.Open（）; 
 
 DataSet dataset =  new  DataSet（）; 
 MySqlDataAdapter adapter =  new  MySqlDataAdapter（  CALL GetEmployeeRights（1），连接）; 
 
秒表sw = 新秒表（）; 
 sw.Start（）; 
 adapter.Fill（dataset）; 
 sw.Stop（）; 
  //  在标签中写入执行时间 
  string  ExecutionTimeTaken =  string  .Format（ 分钟：{0} \ n秒：{1} \ n Mili秒：{2}，sw.Elapsed.Minutes，sw.Elapsed.Seconds，sw.Elapsed.TotalMilliseconds）; 
 
 MessageBox.Show（ExecutionTimeTaken）; 
} 
 

此程序只返回70条记录。

 DELIMITER $$ 
 
  CREATE  DEFINER =`cellsmar` @`％ ` PROCEDURE `GetEmployeeRights`（p_EmployeeID  int ）
  BEGIN  
 
  DECLARE  l_EmployeeID  int ; 
  SET  l_EmployeeID = p_EmployeeID; 
 
  IF （l_EmployeeID =  0 ）
 那么 
  SELECT  FormID，FormName，CanAccess，类型 来自 RightsCatalog; 
  ELSE  
  SELECT  
 ER.StoreID 
， ER.FormID 
，FormName 
，IFNULL（ER.CanAccess， 1 ） as  CanAccess 
，RC。类型 
  FROM  EmployeeRight ER 
 < span class =code-keyword> RIGHT   OUTER   JOIN  RightsCatalog RC 
  ON  RC.FormID = ER.FormID 
  WHERE  EmployeeID = l_EmployeeID; 
  END   IF ; 
   -    ****** [Objec ... ts]脚本日期：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 ）
那么
SELECT FormID，FormName，CanAccess，类型 来自 RightsCatalog;
ELSE
SELECT
ER.StoreID
， ER.FormID
，FormName
，IFNULL（ER.CanAccess， 1 ） as CanAccess
，RC。类型
FROM EmployeeRight ER
< span class =code-keyword> RIGHT OUTER JOIN RightsCatalog RC
ON RC.FormID = ER.FormID
WHERE EmployeeID = l_EmployeeID;
END IF ;
- ****** [Objec ... ts]脚本日期：02/24 / 2014 10:00:33 *******

END

在做了一些测试之后，我发现启动的MySql Connector提供的速度比在MySql网站上找到的要快得多。这是链接，以防任何人有兴趣测试两者。 http://www.devart.com/dotconnect/mysql/download.html [ ^ ]。有免费版本，可以使用dotConnect for MySQL 8.3 Express

好的，即使它已经很晚了，你也可以从数据库中获取数据。这意味着您的连接没有问题。



问题可能出在您的数据库设计中，也可能出现在查询中。因此，如果是第一种情况，那么 规范化 [ ^ ]数据库。

如果它是第二个，那么它只是取决于查询的复杂程度，因此要优化。



-KR

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

这篇关于MySQL查询首次执行需要很长时间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！