Displaying data on a datagrid too slow

Question

I have a datagrid (WPF/C#), that's pulling in two fields from a rather large MySQL view. However, it takes far too long to render onto the screen and I wanted to know if there is a quicker way?

private void SetupDataGrid()
{
    try
    {
        _con.Open();
        var com = new MySqlCommand("SELECT `Record ID`, `Company Name` FROM tblTest.all;") { Connection = _con, CommandType = CommandType.Text };
        var dt = new DataTable();
        var sdt = new MySqlDataAdapter(com);
        sdt.Fill(dt);
        DataGridActivities.DataContext = dt;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    _con.Close();
}

how about being efficient and not displaying ALL records? filter your recordset. have you also tried to make the grid invisible then set it visible once bound? — Ahmed ilyas, Apr 22 '14 at 07:28
does it take a lot of time to render the first time, or do you have problems when scrolling ? — Andy, Apr 22 '14 at 08:22
It's the first time - it just takes about 5/6 seconds to display anything (and then all of the records are available). — AndyDB, Apr 22 '14 at 08:42

Mez · Accepted Answer · 2014-04-22T09:17:05.460

2

You could introduce paging into the grid, and only extract from the database the records which you are going to display. Another approach would be to extract data, cache it, and then access the cache - but in this case first time round would still be slow.

If extracting a large data set is the problem, I would consider only extracting the items for the current page.

With MySQL you can do this in the following approach

SELECT * FROM [TABLE] LIMIT 5,10;

edited Apr 22 '14 at 09:17

answered Apr 22 '14 at 07:42

Mez

4,666
4
29
57

Thanks guys - I'm just using this as a worst case scenario (it will be filtered down using a parameter based query). However, I'm sure I read that VS has a new WPF/XAML function that speeds things up by returning the first chunk of data to the grid and then processing the rest as required. – AndyDB Apr 22 '14 at 08:09
Yes I see this as the best approach when having a large data set to load and bind to a grid. I updated my answer with an example. – Mez Apr 22 '14 at 09:16
That's really useful Stephen - thank you. But I'm sure there is a new DataGrid command that will also help. – AndyDB Apr 22 '14 at 10:10
I think this is might be close (but need a bit of help): http://social.msdn.microsoft.com/Forums/en-US/05f43615-e4ca-48cc-bcfa-310989a5eda7/datagrid-how-to-improve-initial-rendering-speed?forum=silverlightcontrols – AndyDB Apr 22 '14 at 10:15
That link shows pages as well. I think and recommend that you first start with pagination and see how that improves performance. If you are still unhappy with the performance, then think of other ways and means. – Mez Apr 22 '14 at 11:28
Thank you everyone for your help. this idea makes the most sense. – AndyDB Apr 22 '14 at 12:02

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

Which part is slowing down? Have you tried to time the SQL query?

Never used MySQL this way, but usual optimization techniques are stored procedures and indexes.

Unless you really want to display all the records in the same page, you should consider adding pagination in your query.

edited May 23 '17 at 12:10

Community

1
1

answered Apr 22 '14 at 07:36

Trajan

339
2
15

Displaying data on a datagrid too slow

2 Answers2