Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries
Hi, On 07.02.20 09:25, Clemens Ladisch wrote: Jürgen Baier wrote: CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); CREATE TABLE staging ( ATT1 INT, ATT2 INT ); Then I execute DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2) which takes a very long time. DELETE FROM main WHERE (att1, att2) IN (SELECT att1, att2 FROM staging); Thank you very much. I can confirm that this solves my problem and indeed scans the staging table and looks up the main table: sqlite> EXPLAIN QUERY PLAN DELETE FROM main WHERE (att1, att2) IN (SELECT att1, att2 FROM staging); QUERY PLAN |--SEARCH TABLE main USING INDEX sqlite_autoindex_main_1 (ATT1=? AND ATT2=?) `--LIST SUBQUERY `--SCAN TABLE staging For reference: This syntax is not supported by Microsoft SQL Server (2017). But Microsoft SQL Server is relatively fast when using the original DELETE FROM query. Thanks, Jürgen Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance problem with DELETE FROM/correlated subqueries
Hi, I have a question regarding the performance of DELETE FROM (or maybe better: correlated subqueries). I have a table "main" and a table "staging". In "staging" I have a subset of "main". I want to delete all rows from "main" which are also in "staging". CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); CREATE TABLE staging ( ATT1 INT, ATT2 INT ); Then I execute DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2) which takes a very long time. As far as I understand the query plan SQLite scans the full staging table for each row in "main": sqlite> EXPLAIN QUERY PLAN DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2) ...> ; QUERY PLAN |--SCAN TABLE main `--CORRELATED SCALAR SUBQUERY `--SCAN TABLE staging How do I speed this up? The idea is that the database should scan "staging" and lookup "main" (because an appropriate primary index exists). But I'm open to any alternative approach. I just have the situation that I have a very large "main" table and a "staging" table which contains also a large number of tuples which should be deleted from "main". Any ideas? Thanks, Jürgen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: DROP TABLE while a cursor is open leads to SQLITE_LOCKED
Hi, sorry for the repost. I just noticed that I probably should have added "Bug report" in the subject line. I'm using the Xerial JDBC driver for accessing SQLite (but this issue is not directly related to the driver). I have the problem that it is not possible to drop a table in the same database connection while a resultset is open: 0. Preparation: Create table t1 and add some values 1. Open connection 2. Create temporary table tmp1 3. Execute SELECT statement on t1 (SELECT * FROM t1). 4. Execute DROP TABLE tmp1 while still iterating over the result of 3. => [SQLITE_LOCKED] A table in the database is locked (database table is locked) Note that all this happens in a single thread. Someone of the Xerial community pointed me to https://www2.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked It seems while a resultset is open it also was not possible in earlier SQLite versions to CREATE a table, but this restriction has been fixed in the meantime. Is it planned to fix this for DROP TABLE too? Or is there some workaround? Thanks, Jürgen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DROP TABLE while a cursor is open leads to SQLITE_LOCKED
Hi, I'm using the Xerial JDBC driver for accessing SQLite. I have the problem that it is not possible to drop a table in the same database connection while a resultset is open: 0. Preparation: Create table t1 and add some values 1. Open connection 2. Create temporary table tmp1 3. Execute SELECT statement on t1 (SELECT * FROM t1). 4. Execute DROP TABLE tmp1 while still iterating over the result of 3. => [SQLITE_LOCKED] A table in the database is locked (database table is locked) Note that all this happens in a single thread. Someone of the Xerial community pointed me to https://www2.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked It seems while a resultset is open it also was not possible in earlier SQLite versions to CREATE a table, but this restriction has been fixed in the meantime. Is it planned to fix this for DROP TABLE too? Or is there some workaround? Thanks, Jürgen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
