Microsoft small business knowledge base

Article ID: 274112 - Last Review: February 28, 2014 - Revision: 1.7

This article was previously published under Q274112
This article has been archived. It is offered "as is" and will no longer be updated.


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:
 "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:
317032  ( ) 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 Online.

  • 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 SQL Server 2000 Windows CE Edition
kbnosurvey kbarchive kbinfo KB274112
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support