Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation