Slow first query in SQL CE server 3.5

Question

Hi,

I have a Windows CE 6 device and am running a VB.NET app, CF 3.5 using SqlServerCE 3.5 SP1.

My app opens a connection to a server ( SQL server 2008 ) and loads data which is stored in the SQL database on the device.

When this is done first thing in the morning, the first query takes several minutes, but after that, query speed is rapid and < 1 secong.  If the database is refreshed later during the day, using the code module, the first query runs as fast as normal.

I know there are issues to do with character based indexes on databases moved from servers to devices, but that does not apply in this case as the table persists on the device, and has data written to it resulting from queries to the remote SQL2008 database.

I could also understand if there was a slow query every 'first' time after a data refresh, but that's not happening.

Can anyone suggest a reason why I would be seeing the behavior ?

Many thanks

Mart

Answer 1

Hi MPH1956,

According to description, it seems that you load data from SQL Server 2008 and cache the data on your device, after that, your application gets data from your device. Is this true? If so, I want to inform that it require time to download data from SQL Server 2008 to local device. If we want to improve the delay when we execute our first query, we need to improve our synchronization method and decide which data will download from SQL Server 2008, for example, we can just download the changed data rather than downloading all the data again. Additionally, please post more detail information about how much data we need to download from SQL Server 2008, and how your application check and decide it needs to download data from SQL Server 2008.

---

Allen Li
TechNet Community Support

Answer 2

Thanks for the feedback Allen.  I don't think the data transfer time was the issue.

I did find that the main cause was an issue outside of my application, but I did also find that removing character based indexes on the device improved the situation.

I have read elsewhere, that due o loacle change transfer of data from host to device can result in index changes on the device, and I have to put it down to that.

Thanks, Martin