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:
Use of Indexes
- CPU speed.
- CPU instruction set.
- Network speed (for connectivity applications).
- Memory speed.
- Memory size.
- Database size.
- Query complexity.
- Use of indexes.
- Other database issues.
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:
"SELECT * FROM tablename"
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:
HOW TO: How to Change the Temp Database Location in SQL Server CE For SQL Server CE 2.0
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