Roy Osherove

View Original

Quick & Easy ADO.NET Speed Improvements

OK, I coudn't resist...

1) It’s about 20 times faster to create DataTables in your dataset using code, than letting your DataAdapter create them.

2)  A very good potential for speed improvement lies when filling a DataSet with DataTables that have constraints such as PrimaryKeys.
As the DataAdapter adds each row to the DataTable, it has to check that the new row meets the constraints of the table. This means significant slowdown when loading a dataset. Usually, your dataset will have the same constraints as the database you pulled the data from, meaning there’s no reason to check validity of data again.
Easy solution -  the DataSet contains a property called ‘EnforceConstraints’ which is by default set to True. Just set this property to False before filling up the Dataset and you’ll save all that precious validation time.

If you're working with just a single DataTable object, you can use the BeginLoadData and EndLoadData methods of the DataTable to achieve the same goal.