Archive for September, 2009

BizSpeed completed a mobile project for a client whose field data could result in mobile database files greater than 300MB.  Our client had already developed a desktop system and wanted to “mobilize” it for field workers.  The application is fairly complex and requires the ability to analyze significant history in the field, disconnected from a network.  This article should help anyone considering a large scale SQLCE database to understand how to scale and provides some tips for database and application design.

Background

The application is used for field data capture in the energy industry.  The data is composed of several hundred items, each with up to a year’s worth of historical transaction data.  The database is structured such that transactional data is stored very generically and is transformed through several tables representing a data dictionary.  It is a highly flexible approach providing an adaptable UI and data structure.

The bulk of the application UI is built from declarative XML that matches the properties in the data dictionary and resolves the correct UI input control based on the type of data represented by the property.  Data is bound to the forms using traditional .NET databinding techniques against standard System.Data.DataTable objects.

Performance Notes

Our performance testing showed revealed a few interesting facts.

Fact 1 – memory vs. database size

One is that the memory footprint of the application was generally unaffected by the overall size of the database file, as illustrated by this chart:

Fact 2 – Performance based on number of rows returned vs. size of db

Second is that the overall performance of the screen loading process was only really dependent on the number of rows returned by the particular query being executed and not the overall size of the database or the total number of rows in a given table.  We can see this in the following graph.

Simply increasing the number of rows in the various transaction tables by increasing the number of items and their transaction history makes little to no difference on the time it takes the application to load an individual screen.

This graph demonstrates the impact of reducing the “Event Window,” or the time horizon for retrieving past history for a particular item edit screen.  In all but the last data point, the Event Window is the previous 210 days out of a possible 366.  The final data point specifies an event window of 30 days.  The blue/diamond line represents a screen that gets its data from aggregating history from several different transaction types, what we call a “multi-input form.”  The other lines represent simple single-transaction forms that only get their data from one table.  It is clear from this graph that reducing the “Event Window” has a very dramatic  impact on the performance of these multi-input forms.

Fact 3 – First use of new versus existing database can affect performance

One interesting phenomenon we noticed was in the “First Use” case, when the application was accessing a freshly created database.  The actual database files being used on the devices are generated by a process initiated from the desktop application.  In this scenario, we observed a substantially longer application startup/login time on a “fresh” database than on a “used” database.

Tips for scaling complex apps and large SQLCE databases

• Avoid outer joins in SQL queries.
• Use the SQLCE profiler to load your databases and queries. Ensure you don’t have queries that cause table scans.
• Restrict as much as possible the number of rows returned from your queries.
• For large, complex mobile forms, the number of items on the forms and databinding will affect performance. When forms get larger, you should consider caching the screens after building them.  One of the biggest performance improvements we achieved was when we started caching the screens after we initially built them.  We were able to achieve significant savings since most of the time, the field users pull up the same screens but for different items.  Caching the screens allows us to save up to a third of the total time taken to instantiate them.
• When running a large app that performs frequent file system operations, such as event logging, setup the logging so it can be turned on in a debug mode but in release mode it only writes critical events. We performed significant tuning on the mobile app and in order to reduce determine where the delays were, we logged everything. Small 5msec writes are not a big deal but 500 of them will quickly impact user performance.  Additionally, there are differences in different vendors memory models (Motorola, Intermec, Trimble) and some are better at writing small blocks to memory versus others.

Conclusion

The conclusion we can draw is that, as long as you create smart indexes on your tables and do a sufficiently good job of restricting the size of the datasets returned, there is no reason to be concerned about the size of your mobile database and it is only governed by the amount of storage available on the device.