Microsoft KB Archive/303004

= FIX: SQL Server CE Delete Query with a Subquery Over a Large Table Might Stop Responding (Hang) =

Article ID: 303004

Article Last Modified on 4/26/2007

-

APPLIES TO


 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition 1.1

-



This article was previously published under Q303004



BUG # 2760 (SSCE)



SYMPTOMS
A Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) Delete query, with a subquery over a large table, might appear to stop responding (hang). For example, a query similar to the following might take a long time to return if the tables t1 and t2 are large: delete from t1 where col1 in (select t2.col1 from t2 where t2.col2=&quot;something&quot;) Creating indexes on the relevant columns of the tables might not solve the problem for this particular query.



CAUSE
SQL Server CE does not optimize delete queries to use indexes. This causes SQL Server CE to perform a file scan of a really big table in this scenario.



RESOLUTION
To resolve this problem, install Microsoft SQL Server 2000 Windows CE 2.0.

SQL Server CE 2.0 adds optimization for Delete and Update queries to use indexes. The workaround described in this article might still be useful for complex queries on large databases that use SQL Server CE 2.0.



WORKAROUND
To work around this problem you can either:
 * Use a cascading delete.
 * Manually build the subquery results.
 * Use two recordsets and a Seek.
 * Upgrade to a device with more memory.
 * Write the application by using the OLE DB API.

For more information about these workarounds, see the &quot;More Information&quot; section.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Windows CE Edition Service Pack 2.0.



MORE INFORMATION
The following are general workarounds to achieve reasonable response time for a Delete operation with a lookup from another table:  Use a cascading delete.

If applicable, a cascading delete might be faster. For example, delete the row in table t2 and have it cascade to table t1 by using an ON DELETE CASCADE constraint.

However, FOREIGN KEY constraints require that each value in the column to exist in the specified column of the referenced table.  Manually build the subquery result.

Build the subquery result manually, and then build a final query from the results. For example: select id from t2 where name=&quot;lookup_name&quot;

From the resulting list, make a query similar to: delete from t1 where id in (row1id, row2id, ...) You must find a suitable threshold where you can stop building the IN clause and run the query. Otherwise, this method may require a lot of memory to build the IN clause.  Use the Seek method.

Use the Seek method to look up values in the outer table, delete the values, and then use the subquery table as the source for values.  Original Query delete from t1 where col1 in (select t2.col1 from t2 where t2.col2=&quot;something&quot;) Here is sample Microsoft eMbedded Visual Basic code that uses two ADOCE Recordsets and a Seek: ' ======================================== ' Reverse Index Match using Seek ' ======================================== Dim cn As Connection Dim rs1 As Recordset Dim rs2 As Recordset

Set cn = CreateObject(&quot;ADOCE.Connection.3.1&quot;) Set rs1 = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) Set rs2 = CreateObject(&quot;ADOCE.Recordset.3.1&quot;)

cn.Open &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\simple.sdf;&quot;

rs2.Open &quot;select t2.col1 from t2 where t2.col2='Test3'&quot;, cn, adOpenDynamic, adLockOptimistic MsgBox &quot;rs2 first row is &quot; & rs2.Fields(&quot;col1&quot;)

rs1.Open &quot;t1&quot;, cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

rs1.Index = &quot;index_over_t1_col1&quot; Do While Not rs2.EOF rs1.Seek rs2.Fields(&quot;col1&quot;).Value, adSeekFirstEQ MsgBox &quot;FirstEQ &quot; & rs1.Fields(&quot;col1&quot;) rs1.Delete rs1.Update rs2.MoveNext Loop rs1.Close rs2.Close cn.Close Set rs1 = Nothing Set rs2 = Nothing Set cn = Nothing   </li> Upgrade to a device with more memory.

If your database is on secondary storage, for example a flash card, consider upgrading to a device with more memory to keep the .sdf file in memory. It takes less time to access data in memory instead of on the flash card.</li> Write the application by using OLE DB API.

Write your code directly to the OLE DB layer instead of the ADOCE layer in eMbedded Visual Basic, which may additionally improve the performance.</li></ul>

<div class="references_section">