慢SQL查询性能 [英] Slow SQL Query Performance

问题描述

我正在研究一个查询MySQL数据库的C＃WinForm应用程序。查询所需的时间令人难以置信（30秒 - 1分钟）。我对DB'的经验很少，我似乎无法弄清楚我的查询有什么问题。有人可以指出我做错了什么。

I''m working on a C# WinForm Application that queries a MySQL database. The time it takes to query is unbelievable slow (30secs - 1min). I have very little experience with DB''s and I can''t seem to figure out what''s wrong with my query. Can someone please point out what I''m doing wrong.

string sql = "SELECT t1.date, t1.name, t2.name, t3.addrs
             "FROM tbl1 t1 JOIN tbl2 t2 ON tbl1.id = tbl2.id
             "JOIN tbl3 t3 ON t3.addrs = t2.addrs WHERE t1.date = ''2013-04-01'';";

string connStr = "Server = 10.10.10.100; Database = mydb; etc...";
MySQLConnection conn = new MySQLConnection(connStr);
MySQLCommand    cmd  = new MySQLCommand(sql, conn);
MySQLDataReader rdr = null;
DataTable dt = new DataTable();

dt.Columns.Add("Id","FirstName","LastName","Address","Date");

rdr = cmd.ExecuteReader();

while(rdr.Read())
{
  dt.Rows.Add(rdr["ID"], rdr["FirstName"], rdr["LastName"], rdr["Address"],rdr["Date"]);
}

conn.Close();
rdr.Close();

dataGridView.DataSource = dt;

推荐答案

我不明白为什么你加入tbl3。 t3.addrs = t2.addrs ，因此你可以 SELECT t1.date，t1.name，t2.name，t2.addrs ，不是吗？

或者tbl3中的记录中是否存在tbl3中没有记录，并且您希望仅在两个表中都存在记录？

此外，您可以在SQL Server管理工作室中执行querey。查看查询的执行计划，它可能会给你一些提示（例如表扫描而不是使用索引）。

I do not understand why you join tbl3. "t3.addrs = t2.addrs", consequently you can "SELECT t1.date, t1.name, t2.name, t2.addrs", can''t you?
Or is it possible that no record exists in tbl3 for a record in tbl2, and you want to get records existing in both tables only?
Furthermore, you can execute the querey in SQL Server management studio. Look at the execution plan of the query, it might give you some hints (e.g. "table scan" instead of use of an index).

这篇关于慢SQL查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！