INFO: SQL Server CE Performance Tips and Efficient Memory Handling

This article describes how to design a SQL CE (SSCE) application to improve performance and to minimize memory usage.

The performance of a SQL CE application may vary based on a number of factors. These factors include:

• CPU speed.
• CPU instruction set.
• Network speed (for connectivity applications).
• Memory speed.
• Memory size.
• Database size.
• Query complexity.
• Use of indexes.
• Other database issues.

Use of Indexes

If you are using a WHERE clause, ORDER BY or JOIN, an index on the appropriate columns can improve performance tremendously. However, if you are running code similar to: then indexes will not help.

Minimizing Memory

Memory is constrained on the device. There are ways to minimize memory usage in SSCE. Here are a few tips:

• If you are using a query, return back only the columns or rows you need. For example:

  Select col1, col2 From tablename Where search_condition

• If you do not need scrollability, use a forward-only cursor, adopenforwardonly or adlockreadonly, which substantially reduces memory usage.
• Avoid unnecessary ORDER BY, DISTINCT, or GROUP BY operations, which can use more memory than other operators.
• You may consider switching from a query to operating directly on the base table. You can use Seek to find the rows you want, assuming that there is an index on the columns in your WHERE clause. For more information, please refer to the "Seek" topic in SQL CE Books Online.

By default, SQL Server CE creates temporary database files in the Temp folder on the Windows CE device. You can move the temporary database files to a CF card to make more space available in the main storage. For SQL Server CE 1.1, use the following Knowledge Base article as a guide: Additionally, see the "Using SQL Server CE Temporary Databases" topic in SQL Server CE Books Online.

See the "Building Applications" topic in SQL Server CE Books Online.