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

MORE INFORMATION

• 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="lookup_name"

  
  
  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="something")

  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("ADOCE.Connection.3.1")
  Set rs1 = CreateObject("ADOCE.Recordset.3.1")
  Set rs2 = CreateObject("ADOCE.Recordset.3.1")
  
  cn.Open "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\simple.sdf;"
  
  rs2.Open "select t2.col1 from t2 where t2.col2='Test3'", cn, adOpenDynamic, adLockOptimistic
  MsgBox "rs2 first row is " & rs2.Fields("col1")
  
  rs1.Open "t1", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
  
  rs1.Index = "index_over_t1_col1"
      
  Do While Not rs2.EOF
          rs1.Seek rs2.Fields("col1").Value, adSeekFirstEQ
          MsgBox "FirstEQ " & rs1.Fields("col1")
          rs1.Delete
          rs1.Update
          
          rs2.MoveNext
  Loop
   
  rs1.Close
  rs2.Close
  cn.Close
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set cn = Nothing	

• 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.
• 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.