How can I improve the loading of my datasets?

Datasets maintain some internal indexes to improve performs for things like finds and selects.

When you are loading more than 1 row into a DataTable - with a DataAdapter.Fill or other technique, you can turn this index maintenance off by doing a


Where MyTable is the reference to your DataTable, which could be


When you are done loading the data, don't forget to turn the index maintenance back on with.


When loading multiple tables, you should turn on/off 1 table at a time.

Another thing that Datasets do while having rows added to tables is to validate them. Things like primary key uniqueness, foreign key referential integrity and nulls in columns with AllowDBNull = false are some examples of things that must be checked. Again, you can save some cycles by turning this off during loading of a dataset and turning it back on afterward. This can be done with:

MyDataSet.EnforceConstraints = false

And of course when you are done loading, you can perform a:

MyDataSet.EnforceConstraints = true

Of course you may get a “ConstraintException” on this last line if there are problems with your data. Otherwise, you'd get this exception as soon as the offending row is loaded. On a related note, you can check DataSet.HasErrors and each DataTable.HasErrors for any errors. For each table you can call DataTable.GetErrors() to get a list of rows with errors. Each row has a RowError property that contains any error text related to the entire row, and also a GetColumnError() method that you can use to test each column for a column specific error message.