DataGridView slow at redrawing

Question

I posted yesterday as I suspected a LINQ-to-SQL query I had written was painfully slow. What I am doing is getting iterating over the results of my query, and constructing a DataTable from it. I then bind the DataTable to a DataGridView on my form.

I thought the query was the slow part, but I have disproved this - the query, and also the DataTable construction, takes a second or two at most, but when I set the DataSource property on the DataGridView, the app seems to hang for about 10 seconds before it shows the grid.

I've tried setting the DoubleBuffered property on the DGV using reflection, but it sadly made no different. Has anyone else experienced any issues like this?

EDIT: Note, that if the grid has a small amount of rows (i.e. 30-40), it works a treat. As soon as the row count reaches its hundreds, it grinds to a halt

There might be something useful in this MSDN entry (Performance Tuning in the Windows Forms DataGridView Control): msdn.microsoft.com/en-us/library/ms171621.aspx — Adam Houldsworth
I once had a very slow DataGridView with linq-to-sql in the background because a lazy loaded reference was triggered during the databinding. If you are sure that nothing like that is happening then I'd suggest hand generating the datatable of the same size and seeing if you still have the issue - if the issue still persists then your will probably need to implement virtual mode. — David Hall
@David Hall : quite possibly lazy loading, can be solved with dataloadoptions on the datacontext. See also my answer — Pleun

Pleun Pleun · Accepted Answer · 2011-09-07T11:06:45

Trace the SQL generated. Most probably indeed it is lazy loading resulting in one or more extra sql statements for each row (this is consistent with the behaviour you describe).

You can log the SQL from the datacontext or you can use the sql server profiler to see the sql being generated.

If it is indeed lazy loading, you can use DataLoadOptions of the datacontext to override the default lazy loading behaviour. example (this will load all the orders for each customer)

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(Customer => Customer.Orders);
db.LoadOptions = options;

So in this case, you will see a join being generated by Linq2sql

vMore info: http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx

DataGridView slow at redrawing

1 Answers