Getting the best performance out of PocketSql / SqlCe

[Jon Brunson]

Hi,

I'm working on a CF.NET project that uses SqlCe extensively, and at the
moment database access is very slow. So I would appreciate your opinions
and ideas for improving the performance.

Currently I'm using DataAdapters to .Fill() a DataTable (NOT DataSet),
and returning either the first DataRow (if I only want 1 record), or all
of the rows in the returned table, from the database tier to the
business logic. Now I know .Fill() is slow, but how can I improve on
that by doing stuff myself?

I few of my ideas follow:

1) Use a DataReader to populate a HashTable (or a Collection of) and
return that/those to the business logic

2) Use a DataReader to get the data, and create a DataTable using the
DataReader's .GetSchemaTable() method, then add each row from the
DataReader manually (I think this is pretty much what DataAdapter.Fill()
does anyway)

3) Keep using DataAdapter.Fill(), but do something magical that I don't
know of to make it quicker.

Please bare in mind I can't return the DataReader directly, as most of
the time I need more than one open at a time (and as it's SqlCe, I can
only have 1 connection open to the database)

 

[Alex Yakhnin, MVP]

How big is the table?

 

[Jon Brunson]

How big is the table?

The tables vary in size, from around 100's of records, to potentially
10,000's.

 

[Darren Shaffer]

Jon,

From the microsoft.public.sqlserver.ce newsgroup, Kevin Collins recently
posted an excellent presentation on performance tuning SQL CE that he
gave at MDC this year. It is well worth the time to watch:

http://24.17.112.250/SQLMobileWebCasts/mbl348.wmv

-Darren

 

[Jon Brunson]

Jon,

From the microsoft.public.sqlserver.ce newsgroup, Kevin Collins recently
posted an excellent presentation on performance tuning SQL CE that he
gave at MDC this year. It is well worth the time to watch:

http://24.17.112.250/SQLMobileWebCasts/mbl348.wmv

-Darren

Thank-you Darren, I've also found (don't know if it's related to the
link above) a power point presentation from the MS Mobile DevCon called
"SQL Server CE 2.0 Performance, Tuning, Deployment and Scaleability"
from http://www.only4gurus.com/v2/download.asp?ID=5470 - going through
that atm, but it's more about actual SQL code, as apposed to .NET
"options" (although I am only on page 26 of 46)

Seems a visit to one of thses conferences may be in order in the near
future (I'm sure my boss will go for that!! ) )

 

[Ilya Tumanov [MS]]

You have to limit number of records you're keeping in memory, that's the
only way with that many records.
Ask yourself do you need 10K records at ones? Perhaps, you can retrieve
records one by one?
May be in small batches of 10-50-100 records?

If brute force approach does not work for some reason, you have no choice
but to come up with something more efficient.

By the way, it makes no difference if DataTable belong to a DataSet or not.
As to using DataReder to populate Hashtable, I'd say it will be just as
slow as Fill(), most likely even slower.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------