Does a datagridview combobox column affect the speed when commiting it to the database?

Question

I have a form named, frmTransaction, that has txtTranNo, txtCustCode, dtbDate, txtRemarks, txtTotalAmount, and has a datagridview named, dtgDetail.

On the dtgDetail, I have columns cTranNo, nLineID, cProductID, nQty, nPrice, and nAmount.

cProductID is a combobox column that has a datamember cProduct and a displaymember cDescription, so that when it is runnning the user will see the product name instead of its product code.

But my product has 2000 records, and when I load my form it is very slow. It is also slow when saving/committing it to my database.

I think the cProduct column has something to do with my form's speed. Is this true? How can I speed it up?

Edit

I can show the code, but it will less confusing if I just explain how my code works. In my design time, my datagridview has empty columns. The name of the datagridview is also the name of the table that will be bound, and when i run it, it creates its column that automatically binds based on the table's schema. I also have a flag that tells the program if the field will be a combobox column or just plain textbox. If it is a combobox column, it will generate an sql script, and run and save the result to a datatable that will be the datasource of the combobox. The datamember and the displaymember of the combobox column are also determined by the flag.

It is advisable that I shouldn't put any condition in my script for the datasource of my combobox column.

Edit - 12-05-12 I try to filter my sql datasource upon binding it to the datagridview combobox column...and I would say that I helps to speed my form. My problem now is when I add new record in my datagridview that is not include in the combobox. For example, I will bind this script to my datagridview, "select ID,Desc from product where ID in (1,2,3,4,5)". When I insert a new record/item/product in my datagridview which is ID = 6, it will not show the description datagridview...

You should provide the code that shows how you're binding your data. It's likely that you're making the call to bind the DataGridView too often. — Origin
I've done some extensive work with datagridview and I've found that sometimes, optimizing the sql table can help: What does your table look like? — redhotspike
Have you isolated whether tyhe issue is in the UI or in the database? Capture the SQL query and run it directly in the database - fast or slow? Press F12 to open developer tools (in most browsers). Go to the profiling tab, start profiling, and make the thing do it's work. Stop profiling. Where is all the time taken up? in the rendering code or waiting for the database? — Nick.McDermaid
is the combobox already pre-populated or are you populating it for each record? that would cause an extreme increase in speed if you're populating it every time — redhotspike

Ben Tidman Ben Tidman · Accepted Answer · 2012-12-12T13:47:27

How slow is "Very Slow". Are we talking seconds, or minutes?

Try setting the property AutoSizeColumnsMode to None if it's not already. It's been a long time since I've messed with datagridview, but that property has cause performance nightmares for me in the past.

If that's not the problem then I would start adding timing statements scattered through the code that executes when your grid loads and when you save/commit. This will help you track down whats causing the slowness. It's almost never the thing you expect. Something just as simple as:

var start = DateTime.Now;
//do work
var end = DateTime.Now;

Console.WriteLine("Timer spot 1: " + end.Subtract(start));

Start big. Wrap the timer around an entire method. Keep moving this around your code until you (hopefully) find the spot that slowing you down. Then try and narrow in on the exact line. Don't forget to check any events that you may be firing for slowness.

Hope that helps!

Does a datagridview combobox column affect the speed when commiting it to the database?

3 Answers