Click here to Skip to main content
15,036,363 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
Posted
Updated 2-Apr-13 11:51am
v2
Comments
Mike Meinz 2-Apr-13 18:35pm
   
Does your database have indexes on all of the columns that are used for the joins?
d.allen101 3-Apr-13 10:38am
   
no it doesn't but it's still too slow even considering I don't have indexes. Even if I LIMIT the query to 1, it's still takes about 10secs
Mike Meinz 3-Apr-13 15:58pm
   
Indexes are a requirement if you expect to make an improvement in performance. It is the number one step to start with!
d.allen101 3-Apr-13 18:05pm
   
ok, i've done a little reading and i'm not 100% for but I "think" MySQL creates indexes for foreign keys by default, so actually I "think" I am using indexes.

1 solution

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).
   
Comments
d.allen101 3-Apr-13 3:22am
   
it's just an attempt at an example it's not the actually query. select 4 columns that are joined by 3 different tables and filter on a where by datetime....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900