SQLCE: Large SQL Statements

Been dabbling quite a lot recently with SQLCE on Windows mobile devices. I came across something that was a little bizarre with a very large statement that I was executing.

I was performing a DELETE with a WHERE clause using a NOT IN. The NOT IN had approximately 1200 IDs in it. Very cunningly, each time it executed, the device hung with no exceptions. Great!

I haven't been able to find any evidence of issues with executing large statements like this, so I resorted to adding the IDs to a table and then modifying the WHERE clause to use NOT IN (SELECT [MyID] FROM [MyTable]).

This took way too long to run! So I changed it to use a NOT EXISTS instead - what a difference, took less than 1 second rather than the 5 or 6 seconds when using NOT IN!

Original:
DELETE FROM [MyTable]
WHERE [MyID]  NOT IN (1, 2, 3, 4, ...1200)

Revised:
DELETE FROM [MyTable]
WHERE [MyID]  NOT EXISTS (SELECT * FROM [MyIDTable] WHERE [MyTable].[MyID] = [MyIDTable].[MyID])