Performance Tuning SQL Server CE

In most ways, tuning SQL Server CE is very similar to tuning regular SQL Server programs. For example, the proper use of indexing is critical to the performance of SQL Server CE.

The biggest constraint you will have is a lack of memory on the CE-based computer. Because of the limited amount of RAM available on CE-based computers, you will want to minimize using memory as much as possible. Below are some tips to help you reduce the amount of memory SQL Server CE uses:

• In your queries, only return the amount of data you need, no more. For example, don't return unused columns or rows.
• Avoid cursors. But if you have to use one, try to use a forward-only cursor.
• Try not to use DISTINCT, GROUP BY, or ORDER BY clauses.
• Write custom code, such as using C++, to write directly to OLE DB, which bypasses some overhead, reducing memory usage.

[2000 CE] More from Microsoft Added 12-8-2000

* * * * *

There can only be one connection from a Windows CE device to a SQL Server database, but there can be multiple recordsets open. To maximize the performance of this single connection, keep the following in mind:

• Keep the number of open objects to a minimum. Every open object uses additional memory and can contribute to poor performance. Only open objects, such as cursors or recordsets, when you need them, and close them immediately after you are done using them.
   
• If you open a recordset using ADOCE, open a base table cursor using the adCmdTableDirect flag in order to reduce overhead and boost performance. In addition, base table cursors should be opened using either adOpenDynamic and adLockOptimistic for best performance.

[2000 CE] More from Microsoft Added 8-1-2002