Click here to Skip to main content
15,889,852 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm working on a C# winForm project that queries a DB and retrieves approx 800K rows and renders the results to a dataGridView. When I run the sql from MySQLWorkbench or MS SQL Management Studio it only takes about 15-20 secs to run BUT when I run the code from within my C# application it takes at least 30sec-1min to run AND I have "jump through hoops"....he's an example of my code. Can someone please tell what I'm doing wrong here?

// Button Click...
private void btnQuery_Click(object sender, EventArgs e)
{  
    Thread thrd = new Thread(worker);
    thrd.IsBackground = true;
    thrd.Start();
}

private void worker()
{
   int minID = 0;
   int count = 0;

   string strConn = "Server = MyServer; Database = MyDB; Port = 123; etc...";
   
   MySqlConnection conn = new MySqlConnection(strConn);
   MySqlCommand    cmd = new MySqlCommand();
   MySqlDataReader rdr = null;

   grid.BeginInvoke(new MethodInvoker(delegate()
   {
       if (grid.Rows.Count > 0)
           grid.Rows.Clear();
   }));

   try
   {
      conn.Open();  
      cmd.Connection = conn;
      cmd.CommandText = "SELECT id from tbl1 WHERE timeStamp >= 'user specified time' ORDER BY id LIMIT 1";

      rdr = cmd.ExecuteReader();
      if (rdr.Read())
          minID = (int)rdr["id"];
      rdr.Close();

     bool done = false;

     while(!done && minID > 0)
     {
        string sql = "SELECT tbl1.id, tbl1.timeStamp, tbl2.name, tbl2.exp, tbl3.exch, tbl4.vol " +
                     "FROM table1 tb1 " +
                     "JOIN table2 tbl2 " +
                     "ON tb1._id = tbl2.id " +
                     "JOIN table3 tbl3 " +
                     "ON tbl1._exch = tbl.exch " +
                     "JOIN table4 tbl4 " + 
                     "ON tbl4.oid = tbl1.oid " +
                     "WHERE tbl4.date = DATE_FORMAT(tbl1.timeStamp, '%Y-%m-%d') AND " +
                 
        sql += "tbl1.id > " + minID AND trd1.timeStamp <= 'user specified time' LIMIT 1000";
        
        cmd.CommandText = sql;
   
        rdr = cmd.ExecuteReader();
        
        count = 0;
        while(rdr.Read())
        {
           int id = (int)rdr["id"];
           DateTime dt = (DateTime)rdr["ts"];
           //etc...

          grid.BeginInvoke(delegate()
          {
             grid.Rows.Add(id, dt, etc...);
          }));
        }             
     }
     catch(Exception ex)
     {
     }
     finally
     {
        conn.Close();
        rdr.Close();
        cmd.Dispose();
     }
   }
}
Posted
Comments
adriancs 2-May-13 7:58am    
Using Virtual Mode of DGV is the only way to show such number rows.

well if it was up to me i wouldn't try to stuff 800k rows into a grid but if a this is what i client wants, oh well...and i've implemented tried implementing virtual mode with "just in time" page loading. the implementation i'm using actually works better...than "querying on demand".
 
Share this answer
 
Simple. Don't try and stuff 800K rows into the DGV, not even in virtual mode.

As a user, if you made me wade through that many rows, I'd close the application, uninstall it, and then try and suppress the urge to hunt you down and beat you senseless.

Yes, that's how bad an idea it is.

Filter the result set down to something users will tolerate. Since the screen can only show about 50 rows (depending on screen and font size), why are you stuffing 800,000 rows into it??


But, if you must, here's a little woalkthrough on using Virtual Mode[^] in the DGV.
 
Share this answer
 
Comments
d.allen101 2-May-13 9:20am    
lol. ok i agree but i have to get it done.

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