Is the RDA "PULL" slower in SQL Mobile vs. SQL CE 2.0?

I have a CF application I'm porting from VS2003/SQL CE 2.0 to VS2005/SQL Mobile. I make extensive use of RDA and unfortunately there is a huge performance degredation for me going to SQL Mobile. Where most PULL's in 2.0 take no more than a second or 2 to complete, they are taking around 10 or 11 seconds in SQL Mobile. This is the time from when .PULL starts and ends, it does not include any code run before or after the .PULL is issued, so it definitely something during the PULL that is slower.

I have tried settins of 0,1,3, and 6 for the compression level, all gave similar results, around 10 or 11 seconds. I have also tried doing a .SubmitSQL right before the .PULL to see if a sort of cached connection would help, but it only shaved a second off the time in most cases.

These times are using the same IIS server, SQL server (2000 SP3a), and database. I have tried using a 2003 SE device, WM 5.0 device, WM 5.0 emulator, and 2003 SE emulator, all exhibit the same performance degradation.

I really need to figure this out or I can't move the product to 2005 as the synchronization times would be completely unacceptable to our customers. Has anyone else run into this problem or know of a solution to it?

Thanks!

-Manny

Well, I managed to get the time down. After looking at both versions doing Pull's via SQL Trace, I discovered that the time to begin and to execute the things that happen on SQL server was pretty much the same for both, but the difference came after SQL Mobile was done using SQL Server...there was about an 8 or second lag between finishing the database access and the Pull command completing.

I discovered through the SQL Mobile Books Online that SQL Mobile now has logging that can be enabled through the registry. I tried that and then looked at the resulting log and discovered this happening after it was done accessing SQL Server:

2007/01/05 11:36:09 Thread=13BC RSCB=173 Command=CLOS Hr=00000000 Total Uncompressed bytes out = 249
2007/01/05 11:36:12 Thread=13BC RSCB=173 Command=CLOS Hr=80070005 ERR:DeleteFile failed 0
2007/01/05 11:36:15 Thread=13BC RSCB=173 Command=CLOS Hr=80070005 ERR:DeleteFile failed 0

If you look at the times, you'll see that the 2 errors account for about 6 seconds.

I managed to eliminate those errors by setting full control to the Internet Guest user for the windows directory that sqlce30.dll is in. After that fix, my Pulls are now taking around 4 or 5 seconds rather than 10 or 11, about 6 seconds faster!

I'm still not quite at the speed of 2.0, though. I synchronize 70 tables and in 2.0, this is taking around 3 minutes. In 3.0 it's now taking around 4.5 minutes. Still much better than the 13 or so minutes it was taking!

Any ideas how I can squeeze the last 2 seconds out so it's equivalent to the 2.0 version?

Is Sql Mobile a generic database engine?

Hi,

I am about to write an application of the Pocket PC 2003 SE. I plan on using CF2.0 and VS 2005. The application will need to store several different peices of information, I immediately thought of using a database.

I have read somewhere that Sql Mobile 05 will only allow databases on the device via replication with Sql 2005. I don't want to use replication. I just want to store several diffent peices of information for use with my mobile app.

Would Sql mobile be suitable or should I stick to something like persisted ADO Data tables?

Thanks for your help

GrahamWell, so far it seems to be!

I created a CF2.0 PPC 2003 project and inserted a Sql Mobile database. I could then define tables and insert data from the mobile app at runtime.
Graham|||

The answer to your question in the subject is yes.

There are few things to keep in mind. SQL Server 2005 Mobile or it's earlier version SQL Server CE have simpler engine and thus have simpler set of features and somewhat more restrictions.

E.g. a recent post on this forum was about using semicolon to separate statements. It is not strictly required on full version but it is on Mobile.

You can find some additional information here

http://msdn.microsoft.com/SQL/2005/mobile/default.aspx

and by searching MSDN for SQL Mobile specific topics. You can also download Books Online only for SQL Server 2005 and search there. Check this URL (it is referred from the general SQL Server page):

http://www.microsoft.com/downloads/details.aspx?FamilyId=ADC75E35-7245-4038-9B8A-B8FABAEC16DA&displaylang=en

In addition to that – may be a useful feature in the development – if you have SQL Server 2005 (not Mobile version) Management Studio installed, you can create a SQL Server Mobile database (select SQL Server Mobile in server types when connecting in Object Explorer). It may help you to “play with” queries, syntax, etc. It is very likely that you can do the same from VS2005. I haven’t tried that myself.

Regards,
Boris.

|||Yet another URL of interest:

http://www.microsoft.com/sql/ce/productinfo/SQLMobile.mspx