FIX: SQL Server CE Queries with WHERE and ORDER BY Clauses May Run Slow (280082)

The information in this article applies to:

Microsoft SQL Server 2000 Windows CE Edition 1.1 SP1
Microsoft SQL Server 2000 Windows CE Edition 1.1
Microsoft SQL Server 2000 Windows CE Edition

This article was previously published under Q280082

SYMPTOMS

When you work with a SQL Server 2000 Windows CE database, certain queries with WHERE and ORDER BY clauses may run slow. For example, the following query may run slow:

SELECT drug_id,label_name FROM drug WHERE label_name LIKE 'AA%' ORDER BY label_name

CAUSE

SQL Server CE is using the index on the sort (ORDER BY clause) instead of the predicate (WHERE clause).

RESOLUTION

To resolve this problem, obtain and install SQL Server 2000 Windows CE 2.0.

WORKAROUND

Add another column to the sort.

For example:

SELECT drug_id,label_name FROM drug WHERE label_name LIKE 'AA%' ORDER BY label_name, drug_id

STATUS

This bug was corrected in Microsoft SQL Server 2000 Windows CE 2.0.

MORE INFORMATION

In some cases, the SQL Server CE engine may use the index on the sort (ORDER BY clause) instead of the predicate (WHERE clause). If you add another column to the sort, you force the engine not to use the index on the sort, but on the predicate instead.

Modification Type:	Minor	Last Reviewed:	8/25/2005
Keywords:	kbfix kbprb KB280082