Local database best practices for Windows Phone 8

Article
05/20/2016

[ This article is for Windows Phone 8 developers. If you’re developing for Windows 10, see the latest documentation. ]

Starting with Windows Phone OS 7.1, you can store relational data in a local database that resides in your app’s local folder. This topic covers a miscellaneous collection of best practices to consider when working with a local database. For step-by-step guidance about using a local database in your Windows Phone app, see How to create a basic local database app for Windows Phone 8 and How to create a local database app with MVVM for Windows Phone 8.

This topic contains the following sections.

Data binding

Use of data virtualization and skip/take

With ListBox controls, you can use the Skip and Take methods to enable fetching of the appropriate batches of items as the user scrolls through the list. For more information about enabling data virtualization on data-bound ListBox controls, see Improving ListBox Performance in Windows Phone 7: Data Virtualization.

Using the Skip and Take methods ensures that data will not be loaded from the database into memory until it is needed for display in the ListBox control. For example, the following code shows how to retrieve records 501 to 550 from the database.

return
(from f in App.FeedsDB.Feeds                    
select f).Skip(500).Take(50);

Because there is cost to performing this load operation as the user scrolls through the list, this technique should be limited to scenarios where the data set is large (greater than 150 items). For smaller data sets, loading the entire collection into memory and binding to it there is likely to yield better performance.

Two-way data binding

Entities from a LINQ to SQL data context can be two-way data bound to UI controls like any other “plain-old CLR object” (POCO). When data-binding to entities, consider these points:

To maintain a relationship with the local database, an app must bind to the full entity, not a subset projection.
To persist changes made to entities in the data context, use the SubmitChanges method.

Inserts, updates, and deletes

Large batch edits

In rare cases, an app may need to update many or all of the records in a local database. For example, in a table of 10,000 customers, there is a property named CustomerType. The CustomerType property can be assigned the values: Standard, Premiere, or Exclusive. Assume that there are 5,000 customers assigned a CustomerType of Exclusive, and you need to rename this category to Platinum.

One approach that we do not recommended is to select all of the Exclusive customers and loop through each of them to update their type to Platinum. After all of these changes are made to the data context, a call is made to the SubmitChanges method. In this scenario, all 5,000 objects would be loaded into memory, which may be problematic on the device.

Instead, we recommend an approach that is less costly on device memory: perform the updates in batches using separate data contexts. With this approach, employ a query that sorts the list and uses the Skip and Take methods to ensure that the data is being appropriately paged. Once a batch is complete, call Dispose on the DataContext object to ensure that the context and associated cached objects are cleaned-up by the garbage collector. Alternatively, you can encapsulate the DataContext object in a using statement so that it is automatically disposed of.

Enabling fast updates with a version column

One of the easiest ways to optimize the performance of an update operation on a table is to add a version column. This optimization is specific to LINQ to SQL for Windows Phone. For example, in an entity, add the following code.

        [Column(IsVersion=true)]
        private Binary _version;

Implementing this optimization can yield a significant performance improvement for large updates. For an example of this optimization used in a local database app, see How to create a local database app with MVVM for Windows Phone 8.

Submitting changes to the database

For sending changes to the database, determining the optimal batch size and submit frequency is highly dependent on the app scenario. Consider the following:

The SubmitChanges method will not return until the changes are actually persisted to the file system. This can take some time and should not be done lightly. For example, if the app submits changes every time the user selects or unselects a CheckBox control, significant overhead will be incurred.
However, the time required to submit the transaction will grow with the number of changes. If the app attempts to submit too large of a batch as the app is exiting, there is a good chance that it will not complete before the app process is shut down.

In general, you will want to be more aggressive in submitting changes that resulted directly from user action, because there is no way to restore that data if it’s lost. For changes that are recoverable (for example, cached data from a cloud service), you can afford to build up larger change sets. This is because you can always restore the data if it is not successfully committed on exit.

Working with BLOB data

If your app needs to store and retrieve a large amount of binary large object (BLOB) data, you can store that data directly in a local database or directly in the local folder.

You can use the binary(n), varbinary(n), or image data types to store BLOB data directly in a local database. When taking this approach, we recommend that you test the BLOB query performance with different maximum buffer size values. Increasing the max buffer size parameter in the connection string can help improve the retrieval performance of the BLOB, but it will also increase the memory consumption of your app. For more information about connection strings, see Local database connection strings for Windows Phone 8.

Alternatively, you can store BLOB data directly in the local folder. With this approach, your app might store only the path to the BLOB data in a local database. Because local database files also reside in the local folder, BLOB data stored in the local folder and the local database can persist on the device as long as the app is installed. For more information about working with the local folder, see Data for Windows Phone 8.

Minimizing memory usage

INotifyPropertyChanging

LINQ to SQL change tracking works by maintaining two copies of each object. One copy of the object remains as it was originally materialized from the database. The other copy is changed by the app. Then, when a change is submitted, LINQ to SQL can determine which properties have been updated and submit only those changes to the database transaction.

By default, LINQ to SQL will create the two copies of the object when the objects are materialized. Frequently, however, only a handful of objects in the materialized collection will actually get modified within a specific transaction. In this case, there is no reason to keep a second copy of the object.

The INotifyPropertyChanging interface allows the app to notify the DataContext when it is modifying a property that will ultimately be submitted as an update to the database. The DataContext can use that notification as a trigger to create the copy. This way, only the items that are actually changing need to be duplicated.

Implement the INotifyPropertyChanging interface by adding the following code to your entity and then calling the NotifyPropertyChanging method in the setter of each entity property, just before the value is changed.

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify that a property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

For examples of how to use the INotifyPropertyChanging interface, see How to create a local database app with MVVM for Windows Phone 8.

ObjectTrackingEnabled

Mobile apps will often have read-only query scenarios, where the data only needs to be presented, with no opportunity for the user to make changes. In this case, the change-tracking infrastructure can be turned off altogether to save memory. This is done by setting the ObjectTrackingEnabled property on the DataContext to false.

Note

This setting applies only to the DataContext object on which it is set. You can use another DataContext object to perform operations on the same rows that are not read-only.

Execution model considerations

Fast app switching

When the user navigates forward from an app and the operating system puts it into a dormant state, all of the app’s threads are stopped and no further processing takes place. However, there are certain operations performed by the operating system on behalf of the app, including some database operations, which cannot be fully stopped.

In particular, very complex queries like those with significant grouping and sorting can take some time to complete and may run longer than the time allowed for the app to pause. In that case, the app will be fully tombstoned by the operating system. To ensure that your app can take full advantage of Fast App Switching, avoid performing potentially expensive queries during deactivation.

For more information about the dormant state and the Windows Phone app lifecycle, see App activation and deactivation for Windows Phone 8.

Tombstoning

When an app is tombstoned, the underlying database connections are closed. To return to its previous state after tombstoning, an app needs to redo any queries it had performed before it was tombstoned.

Query performance

Indexing

An index is automatically created for the database columns designated as the primary key. Any additional index created on the table after that is referred to as a secondary index. Secondary indexes should be used for any commonly queried property on an entity, including any property that is used to determine the sort order of the result.

The appropriate sort order (ascending or descending) should be used for properties that will be commonly sorted. By default, indexes have an ascending sort order. To explicitly specify sort order in the Index attribute, follow the column name with ASC or DESC to specify ascending or descending sort, respectively. For example, the following entity attribute specifies an index with an ascending sort order on the OrderID column and a descending sort order on the Quantity column.

[Index(Column=”OrderID ASC, Quantity DESC”)]

Compiled queries

By default, LINQ to SQL will translate the LINQ expression tree into the corresponding Transact-SQL statement every time the query is executed at run time. For queries that are executed with great frequency (for example, find the record with this ID), the overhead of generating the corresponding Transact-SQL each time is very wasteful. To avoid this inefficiency, you can use compiled queries. Compiled queries generate parameterized Transact-SQL statements ahead of time, which can then be reused with different values.

Using LINQ to SQL with background agents

Foreground and background interaction

A local database on Windows Phone supports simultaneous use, by both the foreground app and any associated background agents. In this scenario, it is important to consider the following. If one of the database clients (the foreground app or the live agent) ends abruptly while carrying out some database operation, the other connections can become invalidated and must be re-established. In this case, the other clients of the database will receive a SqlCeException exception. When an exception like this is received, apps need to dispose of their existing DataContext objects and re-create them.

Tip

To synchronize access to the local folder between different threads, we recommend using the Mutex class. A mutex grants exclusive access to a shared resource to only one thread. If one thread acquires a mutex, the second thread that wants to acquire that mutex is suspended until the first thread releases the mutex. For example, a mutex could be used to synchronize access to a folder between a Windows Phone app running in the foreground and a background agent.

Resource constraints

Foreground apps receive a significant portion of the device’s resources (in particular, up to 90 MB of RAM). In contrast, live agents running in the background are highly constrained. In general, aim to divide up your app’s background work into small, manageable chunks that can be completed independently, and with limited resources. If your app is using a database, this means performing query and update operations in small batches. For example, an RSS Reader app might divide the work of updating articles in its cache by feed, rather than materializing every feed in the database before operating on them. In this way, the app can limit the number of database records that are materialized into objects at any one point in time. This will help prevent the agent from exceeding the memory limitations that have been set by the platform.