How do ADO.NET DataTable Constraints affect performance?

Question

Do constraints on a DataTable (e.g. PrimaryKey & UniqueContraint) make Selects more efficient in the same way that they would in SQL Server? Or is their only purpose to enforce rules on the data?

myDT.Constraints.Add("PK", myDT.Columns["UniqueID"], true); //add a primary key
myDT.Constrinats.Add(new UniqueConstraint(new DataColumn[] { //add a unique constraint for UserID
    myDT.Columns["UserID"], myDT.Columns["UniqueID"]
}));

Would these examples potentially have better performance when looking up data in the DataTable by UniqueID or UserID?

Answer 1

In general constraints slow things down. But for selects the unique constraint can speed things up (but I can find a MS reference), also it may be limited based on the select. Most of the references I find talk about the constraints slowing things down.

So in your case it could improve performance.

http://msdn.microsoft.com/en-us/library/49z48hxc.aspx

Answer 2

I think you are confusing the use of primary keys and constraints (business domain model) with the use of indexes (performance).

A Foreign Key can influence an optimiser, and it is common to create an index on Foreign keys.

In the SQL Server world, a Primary key is often confused with a Clustered index, because more often than than a surrogate key (think auto-increment identity column) is choosen as the Primary Key and Clustered Index.

This article may be of interest: DataSet and DataTable in ADO.NET 2.0.

In response to your comment:

Use a DataView for Repetitive Non-Primary Key Searches If you need to repetitively search by using non-primary key data, create a DataView that has a sort order. This creates an index that can be used to perform the search. This is best suited to repetitive searches because there is some cost to creating the index.

The DataView object exposes the Find and FindRows methods so that you can query the data in the underlying DataTable. If you are only performing a single query, the processing that is required to create the index reduces the performance that is gained by using the index.

When you create a DataView object, use the DataView constructor that takes the Sort, RowFilter, and RowStateFilter values as constructor arguments along with the underlying DataTable. Using the DataView constructor ensures that the index is built once. If you create an empty DataView and set the Sort, RowFilter, or RowStateFilter properties afterwards, the index is built at least two times.

Answer 3

DataTables are implemented using B-trees (or some variation thereof). A quick peek into Reflector shows that there's an Index class plus LiveIndexes property in a DataTable class, which implies there are some indexes, but I don't really know where they are.

From my (truly limited) experience: queries on PK are really fast.