DataGridView 800K行 [英] DataGridView 800K rows

问题描述

我正在开发一个C＃winForm项目，该项目查询数据库并检索大约800K行并将结果呈现给dataGridView。当我从MySQLWorkbench或MS SQL Management Studio运行sql时，运行BUT只需要大约15-20秒，当我从我的C＃应用​​程序中运行代码时，运行至少需要30秒-1分钟并且我已经跳过了箍 ....他是我的代码的一个例子。有人可以告诉我这里做错了吗？

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();
     }
   }
}

推荐答案

好吧，如果这取决于我我不会''尝试将800k行填充到网格中，但如果这是我客户想要的，那么哦......我已经实现了尝试使用及时页面加载实现虚拟模式。我实际使用的实现比按需查询更好......

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".

简单。不要尝试将800K行放入DGV，甚至不是虚拟模式。



作为用户，如果你让我涉及那么多行，我''关闭应用程序，卸载它，然后尝试抑制追捕你的冲动​​，让你失去理智。



是的，这有多糟糕它是一个想法。



将结果集过滤到用户可以容忍的内容。由于屏幕只能显示大约50行（取决于屏幕和字体大小），为什么要在其中填充80万行？





但是，如果你必须，这里有一点点使用虚拟模式 [< DGV中的href =http://msdn.microsoft.com/en-us/library/15a31akc.aspxtarget =_ blanktitle =New Window> ^ ]。

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.

这篇关于DataGridView 800K行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！