Microsoft KB Archive/274112

= INFO: SQL Server CE Performance Tips and Efficient Memory Handling =

Article ID: 274112

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft SQL Server 2000 Windows CE Edition 1.1 Service Pack 1
 * Microsoft SQL Server 2000 Windows CE Edition 1.1
 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q274112



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



MORE INFORMATION
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: &quot;SELECT * FROM tablename&quot; 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 &quot;Seek&quot; 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:

317032 HOW TO: How to Change the Temp Database Location in SQL Server CE For SQL Server CE 2.0

Additionally, see the &quot;Using SQL Server CE Temporary Databases&quot; topic in SQL Server CE Books Online.

