I just upgraded our application from SSCE 2.0 to SQL Mobile. Our app is written in C++, and we use OLE DB for most of our queries, including the routine the downloads and inserts our lookup table data. This application is running on a Dell Axim X51.
Using SSCE 2.0, this routine takes 236 seconds, with most time spent inserting data into various tables (using OLE DB). The resultant database size is 15.1 MB.
Using SQL Mobile, this routine now takes 675 seconds, with a resultant database size of 27.9 MB!! There is a noticable increase in time when the downloaded data is being inserted into the database.
What would be the reason(s) for the slower performance and the increased size of the database This appears to be a monumental step backwards in performance. Any suggestions regarding improving the perfomance and size
SQL Mobile performance/size problems with WM5 device
Hot Topic
- migration to Microsoft SQL server 2005 Enterprise Edition (64-bit)
- changing date formats
- My Mobile DB.
- How do i Sort the scripts in my project?
- Scripting multiple procedures in Management Studio
- Changes not committed in SQL Server Project
- SSL issue while uninstalling or installing RS
- Dropdown for dataset stored proc parameter - SSIS 2005
- Can't Design a Script
- Basic Connection
SQL Server
- how to get the rowNumber in a matrix?
- Custom Assembly Class Not defined
- Using linked servers in SSIS package
- How to Fetch the Recrods from MS Access and using it in Script Task Using Control Flow Tools(Execute SQL Task)
- Understanding ADO
- Multi-Instance
- Creating SQL 2005 Logins during installation
- Dumb questions on attribute relationships and multiple hierachy relationships
- A "not in"-lookup?
- Seperate SQL 2K Publisher, SQL2K Distributor, SQL2K Subscriber
SQL Mobile performance/size problems with WM5 device
Joris van de Klundert
We are using OLE DB to do the inserts, there are no query statements, everything is done at the COM level eg hr = pRowsetChange->InsertRow(DB_NULL_HCHAPTER, hAccessor, pData, NULL);
jschroedl
you can try running a SqlCeEngine.Compact() after you are done loading the initial database to get the size down. for your inserts and updates, are you using parameterized queries (DML), preparing them, and reusing them for subsequent statements that differ only by param values
Darren
Alex McCool
I wish I had a better answer - the NTEXT size increase with SQL Mobile is not something you can change other than to maybe switch to nvarchar() on that column. Any chance you could look at a fast storage card to put the SQL Mobile database on With most WM5 devices, there is so much on the device from the OEM that there ends up being about 22-30MB free memory to start with and you don't have much wiggle room with a 15MB SQL CE or SQL Mobile database consuming a big chunk of it.
Darren
normschaef
I suspect your comparisons are from SQL CE on PPC 2003 to SQL Mobile on WM5. WM5 uses FLASH storage as memory, which is significantly slower than the RAM that PPC2003 uses. As far as the footprint on device being larger, the SQL Mobile storage engine uses a different algorithm in terms of spanning data across 4k blocks - I suspect you have columns of type NTEXT in your database - this is the only scenario I have seen where the resulting footprint of a SQL CE database that is converted to SQL Mobile ends up being larger.
If you were to compare your SQL CE 2 application on PPC 2003 to SQL Mobile on PPC 2003, you would likely see a performance improvement, but the database footprint would likely still be larger.
Darren
Angz
As for the size, we must use ntext for some of our fields. The database size nearly doubled! Our customers our already experiencing memory constraints with many of the new devices (especially ones with integrated phones), so this issue will prohibit us from using SQL Mobile.