StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite: Using LIMIT for big update and is it worth dropping indexes
text
Body
copied!<p>I need to update lots of records in one table (around 300k to 500k) and delete several million in another table. As this takes some time, i'm using LIMIT to do this in block so I can show a progress bar to the user.</p> <p>My first small question is, why can I uses the following statement in SQLiteSpy but not when I use the ADO.NET provider System.Data.SQLite ?</p> <pre><code>UPDATE Table1 SET Status1 = newValue1, Status2 = value2 WHERE Key1 = key1Value AND Status1 = value1 LIMIT 1000 </code></pre> <p>I have to use the following statement to make it work:</p> <pre><code>UPDATE Table1 SET Status1 = newValue1, Status2 = value2 WHERE Key1 = key1Value AND Key2 in ( SELECT Key2 FROM Table WHERE Key1 = key1Value AND Status1 = value1 LIMIT 1000) </code></pre> <p>I am using the latest version of SQLiteSpy (which uses SQLite 3.7.2) and System.Data.SQlite.</p> <p>My other question is more complex. I am using 2 tables:</p> <pre><code>CREATE TABLE Table1 ( Key1 INTEGER NOT NULL, Key2 INTEGER NOT NULL, ... Some fixed varchar data fields ... Status1 CHAR(1) NOT NULL, Status2 VARCHAR NULL, Status3 CHAR(1) NOT NULL, UpdateDate DATETIME NOT NULL, CONSTRAINT PK_Table1 PRIMARY KEY (Key1 ASC, Key2 ASC)) </code></pre> <p>and</p> <pre><code>CREATE TABLE Table2 ( Key1 INTEGER NOT NULL, Key2 INTEGER NOT NULL, Key3 INTEGER NOT NULL, ... Some fixed varchar data fields ... CONSTRAINT PK_Table2 PRIMARY KEY (Key1 ASC, Key2 ASC, Key3 ASC)) </code></pre> <p>with on table1 two indexes:</p> <pre><code>CREATE INDEX IDX_Tabel1_Status1 ON Table1 (Key1 ASC, Status1 ASC, Key2 ASC) CREATE INDEX IDX_Tabel1_Status2 ON Table1 (Key1 ASC, Status2 ASC, Key2 ASC) </code></pre> <p>As you might have guessed, Key1 and Key2 in both tables are liked.</p> <p>What I want to do, is delete in Table2 all records for records who have a certain status in Table1 and reset the 3 status fields to their original value and update the date in Table1. As the number of records involded can be big (Table1 contains up to 500k records and Table2 between 20M to 40M), and most of the time it concerns between 50% to 100% of table1, I perfom the deletes and updates in "small" blocks (somewhere between 1000 and 10000 records in Table1). So i repeat the 2 following statements until all concerned records are deleted/updated (one delete and update per transaction):</p> <pre><code>DELETE FROM Table2 WHERE Key1 = @Key1 AND Key2 in ( SELECT Key2 FROM Table WHERE Key1 = @Key1 AND Status1 = @Status1 LIMIT 1000) UPDATE Table1 SET Status1 = @NewStatus1, Status2 = @Status2, Status3 = @Status3, UpdateDate = @Date WHERE Key1 = @Key1 AND Key2 in ( SELECT Key2 FROM Table WHERE Key1 = @Key1 AND Status1 = @Status1 LIMIT 1000) </code></pre> <p>The deletes are pretty fast, but the updates take a lot of time (around 2 to 3 seconds for 1000 records). I guess it's because the both indexes need to be updated. So i wonder if it would improve performance to drop both indexes before the delete/updates and recreate them afterwards. But then the subselects would be become slower. At what point (percentage of the total records involved, or absolute number of records), should i consider dropping the indexes ?</p> <p>Thanks, Marc</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload