You create an application that is based on Microsoft SQL Server 2005 Compact Edition. SQL Server 2005 Compact Edition is running on a desktop computer. When the application calls the SqlCeDataReader.Read function on a large query that joins many tables, one of the following symptoms occurs:

• The application seems to stop responding.
• The query runs for a long time.

This problem frequently occurs if the query contains parameters in predicates.

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

No prerequsite is required.

Restart information

You do not have to restart the computer after you apply this hotfix.

Registry information

You do not have to change the registry.

Hotfix file information

This hotfix contains only those files that are required to correct the issues that this article lists. This hotfix may not contain all the files that you must have to fully update a product to the latest build.

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

How to install the hotfix

1. On the computer where you create the application, uninstall SQL Server 2005 Compact Edition in the Add or Remove Programs Microsoft Management Console (MMC) snap-in.
2. On the computer, double-click the updated SQLServerCE31-EN.msi file.

To work around this problem, follow these steps:

1. Update the query to generate a different join order. To do this, use one of the following methods:
   • Rearrange the order of predicates in the query.
   • Use the FORCE ORDER query hint to force the join order. After you apply the FORCE ORDER query hint, the query optimizer does not select the join order. Instead, you select the join order.
     
     For more information, visit the following Microsoft Developer Network (MSDN) Web site:
     http://msdn2.microsoft.com/en-us/library/ms174646(SQL.100).aspx (http://msdn2.microsoft.com/en-us/library/ms174646(SQL.100).aspx)
   • De-normalize the joined tables.
     
     Note If tables in a database do not have many columns, the database is considered a normalized database. If some tables in a database have many columns, the database is considered a non-normalized database. For example, a database that contains tables that have fewer than 5 columns is considered a normalized database. A database that contains tables that have more than 50 columns is considered a non-normalized database.
     
     For more information, visit the following MSDN Web site:
     http://msdn2.microsoft.com/en-us/library/ms191178.aspx (http://msdn2.microsoft.com/en-us/library/ms191178.aspx)
2. In SQL Server Management Studio, run the updated query.
3. In the execution plan of the query, compare the value of the Estimated Number of Rows item for the join of the original query with that of the updated query. For more information about how to obtain the execution plan of a query, visit the following MSDN Web site:
   http://msdn2.microsoft.com/en-us/library/ms172899.aspx (http://msdn2.microsoft.com/en-us/library/ms172899.aspx)
4. Repeat step 1 through step 3 until you find a better join order.

For more information, view the following topics in SQL Server 2005 Compact Edition Books Online:

• Query Performance Tuning
• Query Hint

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

When the large query has no parameters in the predicates, you may also encounter this problem when the following conditions are true:

• The joined columns are not indexed.
• The joined columns are indexed, but do not have associated statistics.

The large query that this article mentions has the following characteristics:

• The query joins many tables.
• The product of cardinality of each table in the join operation exceeds the upper limit of the int data type.
  
  Note The upper limit of the int data type is 2^31-1.
  
  For example, the query joins Table 1, Table 2, and Table 3. The cardinality of Table 1 is C1. The cardinality of Table 2 is C2. The cardinality of Table 3 is C3. The product of cardinality of each table is C1*C2*C3. If C1*C2*C3 is more than 2^31-1, this problem occurs.

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: