AddIndex

Suboptimal query performance is sometimes caused by table scans, where a query causes every row of a table to be examined. Depending on the kind of data in a column, bottlenecks of this nature can often be rectified by placing an index on the column.

The need for an index may be discovered after a table has been populated with a substantial number of records, and after an app has been published to the marketplace, or the need may arise with the introduction of a new feature that causes the table to be queried in a different way. In both cases, adding an index to an existing table is done by decorating the entity class with an Index attribute, and then by calling DatabaseSchemaUpdater.AddIndex.

For example, if we add an EmailAddress property to the TwitterFriend class, and later decide to add an index to improve query performance, we would first decorate the TwitterFriend class with an index attribute, like so:

Click here to view code image

[Index(Name = "EmailAddressIndex",
       Columns = "EmailAddress", IsUnique = true)]
public class TwitterFriend : NotifyPropertyChangeBase
{
...
}

After it is decorated with the Index attribute, the schema can be updated to include a new index for the column, as shown in the following excerpt:

Click here to view code image

using (DataContext dataContext
            = new TwitterDataContext("isostore:Twitter.sdf"))
{
    DatabaseSchemaUpdater updater
              = dataContext.CreateDatabaseSchemaUpdater();
    updater.AddIndex<TwitterFriend>("EmailAddressIndex");
    updater.Execute();
}