Thursday, November 15, 2007

SQL Server CE Performance Tips

UPDATE: This is a very good article: http://www.sql-server-performance.com/tips/sql_server_ce_p1.aspx

As there have been a few questions recently in the communities regarding Microsoft SQL Server CE performance, I'd thought I'd post the following link which contains some useful information regarding SQL Server CE performance tips: http://support.microsoft.com/default.aspx/kb/274112

Also see here for information of changing the default temp table directory that SQL Server CE uses when processing transactions or large queries from RAM to a storage card which will also improve performance and reduce those annoying OutOfMemoryException exception's.

Also try using the DataReader class over the DataSet and ResultSet wherever possible if you don't need the scrollability, updateable and a bindable cursor as the DataReader will always be more efficient. I myself always use DataReader which populates a strongly typed collection which I can then use to bind to my controls where required.

You might also find this link interesting, written by Chris Tacke of OpenNETCF. It talks about data caching in SQL Server CE to improve performance.

1 comment:

Bronumski said...

Hi Simon, I've been doing investigations on CE for an application at work. On the flip side of your point we have to dump data directly to a table (about 3000) records. So we use the TableDirect command type with SqlCeResultSet (which inherits from SqlCeDataReader). Joao Paulo Figueira over at Pocket PC Developer Network has statistics to colabourate this.

One point on using the data reader, you could use the resultset because this would give you the ability to databind it to a datagrid if you didn't want to populate any objects. This would fetch the data as the user scrolls meaning you wouldn't need to prepare all the data up front.
http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)

P.S. Where's that baby? She must be over due by now.