How to handle a large data amount in C#with MySQL query?

Question

I have a huge amount of data in MySQL exactly 10180 lines and its contionusly updating day by day with another 200-300 line. And in my C# app I have a backgroundworker which gets the data and loads into a datatable then this datatable loads into a ListView which shows the data.

The problem is this took approximately 1 minute which in production is too long.I want to fix that and optimize the code.

The code inside the DoWork method:

            try
        {
            connection.Close();

            if (lvValidate.InvokeRequired)
            {

                lvValidate.BeginInvoke(new MethodInvoker(delegate
                {

                    lvValidate.Items.Clear();
                }));
            }
            else
            {
                lvValidate.Items.Clear();
            }

            System.Data.DataTable dt = DataTransferDA("SELECT * FROM workhours ORDER BY ID desc");

            foreach (System.Data.DataRow row in dt.Rows)
            {
                    ListViewItem tempLv = new ListViewItem(row["ID"].ToString());
                    tempLv.SubItems.Add(row["Date"].ToString());
                    tempLv.SubItems.Add(row["Name"].ToString());
                    tempLv.SubItems.Add(row["WorkCode"].ToString());
                    tempLv.SubItems.Add(row["Assembly"].ToString());
                    tempLv.SubItems.Add(row["Tech"].ToString());
                    tempLv.SubItems.Add(row["Beginning"].ToString());
                    tempLv.SubItems.Add(row["Ending"].ToString());
                    tempLv.SubItems.Add(row["Validated"].ToString());
                    tempLv.SubItems.Add(row["Validated name"].ToString());
                lvValidate.BeginInvoke(new MethodInvoker(delegate
                {
                    lvValidate.Items.Add(tempLv);
                }));


            }
            if (lvValidate.InvokeRequired)
            {

                lvValidate.BeginInvoke(new MethodInvoker(delegate
                {
                    lvValidate.TopItem = lvValidate.Items[topIndex];
                }));
            }
            else
            {
                lvValidate.TopItem = lvValidate.Items[topIndex];
            }
            connection.Close();
        }
        catch (Exception ex) { MessageBox.Show("Error happened: " + ex.Message); connection.Close();} }

10k is a tiny number of rows (lines?) for most databases. Time to tediously add rows to a ListView is where the time is being wasted. DataBinding and a DataGridView would require much less code and be much faster. — Ňɏssa Pøngjǣrdenlarp, Jun 10 '20 at 19:32

score 1 · Answer 1 · answered Jun 10 '20 at 21:10

Some suggestions

Use integer indexing for datarow. Integer indexing can be 3-4x faster. In your loop you are looking up the column index 10 times per row. Lookup the column position once, outside the loop, then inside the loop use the integer index.

int intDateCol = dt.Columns.IndexOf("Date"); ... SubItems.Add(row[intDateCol]);
Use ListView.BeginUpdate() prior to clearing the items and adding them, then call ListView.EndUpdate() when you are done.
If you still have performance issues, try using a ListView in virtual mode.

There is an in-depth look of ListView performance tweaks here

yugami · Accepted Answer · 2020-06-10T20:23:58.213

If I had to guess its the listview that is causing the issue more than the data size.

I did a small test on a db that has 50284 rows. When I used the listview it took forever to show up and my laptop fan kicked into full tilt even though iterating through the data only took 876ms. I switched the test form to a basic listbox and it only took a couple seconds to display after the data loading in the constructor.

I used EFCore to do the data connection/handling but I can't think this caused a huge change in performance from a raw query.

Data(50284) took 876

            int i = 0;
            using (EFContext ctx = new EFContext())
            {
                foreach(data dt in ctx.data.AsNoTracking())
                {
                    i++;
                    listBox.Items.Add(dt.epc);
                }
            }
            watch.Stop();
            System.Diagnostics.Debug.WriteLine($"Data({i}) took {watch.ElapsedMilliseconds}");

[edit] The EFContext is an Entity Framework Core component, but all it does is wrap the database model. I traced it and it just issued a select blah from db.table same as you. It just saves the data in a class format so you can iterate and interact with it like normal code.

I'm currently in Entity Framework mode and had all the mockup code I needed for this test otherwise I would have done a basic SQL connection. The viewer is the issue on this issue not the data size.

Hi, I edited my answer to explain the framework bit a little more but that's definitely not where the speed increase is since it just wraps a SQL query like everything else. The listview vs listbox is where I saw my speed increase. Using ListView just about broke my laptop where listbox worked without an issue. — yugami, Jun 10 '20 at 20:25
Thanks for that. I think the ListView is a really bad idea to handle big data models haha — CommunityProgramAsker, Jun 10 '20 at 20:46

How to handle a large data amount in C#with MySQL query?

2 Answers2