Allow selection of NLS sort algorithm in SQL Server Compact indexes

Allow selection of NLS sort algorithm in SQL Server Compact indexes by James D. Schwarzmeier

Active

Type:	Suggestion
ID:	592059
Opened:	8/31/2010 10:08:49 AM
Access Restriction:	Public

Workaround(s)

view

One of the benefits of SQL Server Compact is its ability to operate across platforms -- both Windows Mobile-based devices used in line of business applications, as well as desktop/server computers. Inserting a huge number of records is fairly slow on some mobile devices, so it is often preferable to pre-build a .SDF database using a server-side ASP.NET app, and copy the binary database down to a device (via web services or another mechanism).

However, because Windows Server and Windows Mobile use different NLS Sort algorithms, indexes must be rebuilt from scratch when the first query is run. We've seen this take 5-10+ minutes for large databases -- an unacceptable performance hit for many applications. Fundamentally, a database which advertises to be cross-platform should have the ability to behave in the same way across each of those platforms.

Details (expand)

English

SQL Server Compact Edition

Ideally, the NLS Sort algorithm could be implemented within SQL Server Compact itself instead of relying on the host operating system's API. Possibly, the algorithm to use could be specified via an API flag or property of the database itself. This would allow database portability and consistent behavior across all supported platforms.

If implementing this within the SQL Compact API itself would be prohibitive on mobile devices due to storage space limitations, I would suggest one of the following:
- Provide the ability to select an algorithm only the desktop/server version of SQL Server Compact.
- Provide a separate API or even command-line utility that would allow a server to reindex a SQL Compact database using the algorithm used on mobile devices.

Improved Performance

Allows for more options when designing an enterprise application's architecture

0 attachments

Posted by IanMcD_ on 3/15/2011 at 8:19 AM

5-10 minutes... your lucky - we have a 55 minute delay! This is a huge issue for us - we are currently asking our customers to stick with XP where possible. Windows 7 is great but if sticking with XP avoids the delay then it'll have to do for today. I would second the idea of a server side conversion utility. Any update from Microsoft on this one? (it been sitting here a while)

Posted by James D. Schwarzmeier on 9/28/2010 at 5:42 AM

Thanks the the response. Yeah, I was afraid that putting that functionality in SQL Compact on the devices themselves would cause too great an increase in the size of SQL Compact. As I mentioned, it might more realistic to do the translation as part of an auxiliary API or command-line utility server-side, where a space increase in the "multiple MBs" range wouldn't really matter. After all, the need is just to be able to port a database between operating systems, so having a conversion mechanism separate from the SQL Compact "proper" is probably simpler.

Thanks again for looking into this matter!
-James

Posted by Microsoft on 9/27/2010 at 4:14 AM

Hi James,

Thanks for logging the issue, and the possible resolutions for the issue. The reason that SQL Server Compact relies on the operating system collation (NLS sort algorithms) is that implementing the algorithm in Compact will increase the size of the Compact runtime from 2 MB to multiple MBs. We will definitely look into different ways to reduce the problem with recreating of the indexes when the file is shared between the different operating systems.

Regards,

Ambrish