When you cache data in memory, you do not need to retrieve the same data from the database each time you need to use it. This can lead to considerably better performance as it cuts down the database round trip required to retrieve that data. Having said that, you shouldn't go around caching everything you use. For example, short lists of data that don't change frequently would benefit more from caching compared to large (for example, 100 records and above) amounts of data.

The following are general guidelines to follow in selecting suitable candidates for data caching:

Now let's take a look at how you can apply data caching to the sales force application. If you recall, you've created the Products table, which contains the full list of products in your application. This same list is retrieved from the database each time you view the details of an existing lead, and is displayed in the Interest tab of the Account Details window. As the list of products is seldom expected to change once it has been synced down from the server, we can cache it in memory to improve performance.

All you need to do to cache the list of products in memory is the following:

Let's make some changes to your code. Declare a public static ProductCollection variable named CachedProducts to the GlobalArea class in your SalesForceApp project (as highlighted in the following code snippet).

public class GlobalArea
{
private static PluginManager _PluginManager = new
PluginManager();
private static Application _Application = new
Application();
private static CustomErrorHandler _ErrorHandler = new
CustomErrorHandler("\My
Documents\Personal\error.log");
private static ProductCollection _CachedProducts = null;
public static ProductCollection CachedProducts
{
get
{
return _CachedProducts;
}
set
{
_CachedProducts = value;
}
}
.
.
.
}

Now let's make some changes to the function that retrieves the list of products from the database. This is the GetProductList() function in the Application.cs file. To see the difference in terms of performance, you can also use the PerfTimer class you've created earlier to display the time elapsed in retrieving the list of products. All these changes are highlighted in the following code snippet:

public ProductCollection GetProductList()
{
DataSet _productsDataset;
ProductCollection _products;
PerfTimer _timer = new PerfTimer();
_timer.StartTimer();
if (GlobalArea.CachedProducts == null)
{
_productsDataset =
GlobalArea.PluginManager.GetActivePlugin.
GetProductList();
_products = new ProductCollection
(_productsDataset.Tables["Products"]);
}
else
{
_products = GlobalArea.CachedProducts;
}
MessageBox.Show("Loading the product list took :" +
_timer.StopTimer() + " milliseconds");
return _products;
}

If you now run the sales force application and try to view an existing account or create a new account, you will see the following pop up. As this is the first time you are accessing this screen, the cache has not yet been loaded and so, a round trip is made to the database to retrieve the list of products. After that, this list is then stored in the cache.

If you close the Account Details window and reopen it, you will see that the load time has now been reduced to a mere 19 seconds (only 10 percent of the original time taken!). This is because it is now retrieving the list of products from the cache.

If you use these types of lists frequently in your application, you can realize huge performance cost savings by caching these lists in the same preceding fashion.

In Chapter 4, you've built a search engine capable of searching through the list of accounts in the Accounts table. When the list of accounts increases into the hundreds or thousands, you will find that the time it takes to perform a search starts to increase. It is not unheard of to have users wait 10 seconds or more for a search to complete. This is, of course, unacceptable in cases where the application is time critical.

You can easily boost search performance without changing a single line of code just by creating an index on regularly searched columns in the database. Indexes allow the database engine to build an internal "map" of your data that allows the database to zoom and narrow down on the exact desired data. Without an index, the database would have to do a full table scan—reading each table row by row to find the matching record.

For example, consider the following SQL statement. Without an index, the database engine would have to scan the entire Accounts table row by row to check if the FirstName matches "John."

SELECT * FROM Accounts WHERE FirstName = 'John'

If you create an index on the FirstName column in the Accounts table, any and all SQL statements that filter data based on the FirstName column will experience a large performance increment. You can create an index in SQL Server CE using the following DDL statement:

CREATE INDEX FirstNameIdx ON Accounts(FirstName ASC)

For the Oracle Lite database, you can create an index using the following DDL:

CREATE INDEX FirstNameIdx ON Accounts(FirstName)

Run these DDL statements using the Query Analyzer or MSql tools provided by SQL Server CE and Oracle Lite respectively. You can also create indexes on multiple columns at the same time. For example, if you know that 80 percent of your users will search using both the FirstName and the AccountType columns, you can create a single index that maps to both of these columns. The following DDL shows how this can be done on SQL Server CE:

CREATE INDEX FirstName_TypeIdx ON Accounts(FirstName ASC, AccountType ASC)

The following DDL shows how this can be done on Oracle Lite.

CREATE INDEX FirstName_TypeIdx ON Accounts(FirstName, AccountType)

Multi-column indexes such as the one we just saw would yield the most optimal performance if users specify both the first name and account type in the search. If the user specifies only the first name or only the account type, the index will not be optimally utilized, and this will lead to little or no performance increment.

Mobile devices don't come with a lot of memory (at least not as much as a desktop PC). A large part of database optimization on the mobile device focuses on improving performance by reducing memory consumption as much as possible. The following guidelines will help you write better code to this effect: