The offline functionality in DataSplice enables fine-grained control over what information is cached for individual users.

Defining the offline data for a mobile application is a balancing act to get the right amount of data for each user:

• On the one hand, it is important to ensure that remote users will have all the information they will need offline. It goes without saying that once they've disconnected, any additional data on the server is inaccessible.
• On the other hand, resources on mobile devices may be extremely limited. As such, they may not be capable of efficiently accessing large amounts of data.

Fortunately, it is possible to control the offline process to a great extent to configure the system such that individual users get exactly the data they need.

Defining Offline Data

The Edit Offline Queries section of the view editor controls the offline behavior of a view.

Query Details

Use the toolbar buttons to add and remove queries from the list. The queries have several common options:

• Query Name - Unique name for the query. This is displayed to the user in the progress status dialog during the offline sync to provide feedback to the user about the action being performed.
• Offline Condition - Specifies whether or the not the query should be performed. If the condition is empty or evaluates to true it is executed and the results added to the offline set.
• Max Records - Controls the maximum number of records to be included by the query. Zero means the result is unrestricted (though many of the backend data sources have additional bounds to prevent runaway queries).

Selecting a query displays an editor to specify the query details. Typically only the filter needs to be specified. The sort information can be used in combination with with the max records setting however. For instance, if a field in the view contains the last time an item was accessed, then sorting on this field and setting Max Records to 100 would make the 100 more recently used records available offline.

Root Queries

These queries are directly executed against the view and the matching records will form the basis of the data available to the user when disconnected. Multiple root queries can be specified and the user will receive the union of the resulting data.

Recursive Queries

For every record made available offline through one of the root queries, the DataSplice Server will run any offline child queries for the record and the resulting records from the target view will be added to the offline set as well. Child queries are created by specifying the parent root query when adding them to the view.

There a couple of points to keep in mind here:

• The server tracks the queries it has already performed to avoid executing the same statement multiple times.
  
• Because of the number of queries needed for recursive queries, they are much slower than root queries and should be used sparingly.

Offline Data Optimization

The offline synchronization event has the potential for poor performance depending on the amount of data being synchronized and the way the views are configured to access that data. Typically some optimization steps are needed to ensure that the end user perception is acceptable.

Synchronization occurs in multiple steps:

1. The client and server exchange digest information of the record checksums available on the client. This allows the server to only send changed data to the client.
2. The server calculates the list of data that needs to be available to the current user by executing the available offline queries.
3. Record that no longer need to be on the client are purged from the local data store.
4. New and updated records are synchronized to the client over the network.

The Remote Client status window is useful to determine which steps are being performed and taking an unacceptable amount of time. Each step is displayed as it is being processed, and the elapsed time and number of records processed is displayed.

Examine each step and work to ensure that everything is working at an acceptable speed. There are a few common methods to improve performance here:

Minimize the Number of Queries Run

Recursive data is great for querying only the data needed offline. This comes at the price of potentially running a large number of database queries. Where possible, replace recursive queries with a single root query in the target view. Even if this makes more data available offline, in many cases the net result will be faster synchronizations.

Minimizing the Records Processed

Not only is this important in reducing the number of recursive queries run, but there is a fixed amount of overhead associated with each record added to the offline set. Reducing the total number here decreases the time and resources needed by the server. In addition, minimizing the amount of data that changes in between synchronizations will greatly speed up the data synchronization process.

Performance of Individual Queries

Optimization of individual queries is a standard database tuning task. Hitting unindexed fields repeatedly during this process can completely kill the overall performance. At a minimum, any queries run as a result of an offline relationship must use indexed fields.

Minimize Purged Records

SQL Server Mobile has fairly poor performance when deleting records. The synchronization is optimized for this, and if the number of records to prune from a table is at least 80% of the total it simply truncates the table and synchronizes all the data. However, if a large number of records needs to be pruned, yet the value is less than 80% of the total records, this can take a long time to execute.

Reduce Individual Record Size

In addition to the total number of records sent to the client, the size of each record affects the synchronization performance as well. Views should be optimized to include only the fields needed for offline usage. Remember that it is not enough to mark the fields as Invisible, since these are still transferred to the client. Fields must be removed from the query generating the view to reduce the record size.