Migrating from SQL Server Compact to SQL Server: Step-by-Step Checklist

Optimizing Performance in Microsoft SQL Server Compact — Best Practices

1. Use appropriate indexing

• Create clustered indexes on primary key or frequently range-searched columns.
• Add nonclustered indexes for frequent equality/lookup filters.
• Avoid over-indexing; each index increases write cost and storage.

2. Keep statistics up to date

• Regularly update statistics so the optimizer picks efficient plans. For SQL CE, schedule periodic rebuilds or re-create indexes to refresh stats.

3. Minimize I/O and file size

• Store only needed columns and use appropriate datatypes (avoid NVARCHAR when VARCHAR suffices).
• Compact and shrink the database file periodically to reduce fragmentation and reclaim space (use CE compact APIs or tools).

4. Optimize queries

• Select only required columns instead of SELECT.
• Prefer sargable predicates (e.g., column = value, column BETWEEN …) and avoid functions on indexed columns.
• Replace correlated subqueries with joins or derived tables when possible.
• Limit result sets with TOP or WHERE clauses for UI queries.

5. Batch writes and transactions

• Wrap multiple inserts/updates/deletes in a single transaction to reduce journaling overhead.
• Use parameterized commands and prepared statements to reduce compile/parse cost.

6. Manage connections efficiently

• Reuse connections (connection pooling in the host app) and avoid frequent open/close cycles.
• Keep transactions short — acquire locks only for the minimum time needed.

7. Reduce locking and contention

• Design operations to work on smaller rowsets.
• Avoid long-running transactions that hold locks; break large updates into smaller batches.

8. Use appropriate concurrency model

• For single-user or low-concurrency mobile/desktop scenarios, prefer lightweight patterns (local caching, sync-only changes) rather than relying on heavy concurrent updates.

9. Profile and measure

• Capture and measure slow queries and hot spots in your app.
• Use execution timing, query plans, and simple profiling (log durations) to prioritize fixes.

10. Plan for migrations and limitations

• Be aware of SQL Server Compact limitations (no server-side procs, limited memory/size). When workloads grow, plan migration to full SQL Server or Azure SQL where advanced tuning (DMVs, Query Store, parallelism) and tooling are available.

If you want, I can generate: (a) a checklist script to find slow queries and missing indexes, or (b) a compact/maintenance script for SQL Server Compact—tell me which.