Re: [sqlite] UPSERT available in pre-release
Perfect, That’s a big step of SQLite. I think one of our projects will benefit of the new upsert. Thanks a lot. wordcount --all :memory: sqlite3.c 2.422 wordcount --insert 2.341 wordcount --insert --without-rowid 3.610 wordcount --replace 1.766 wordcount --replace --without-rowid 1.594 wordcount --upsert 1.625 wordcount --upsert --without-rowid 2.171 wordcount --select 2.281 wordcount --select --without-rowid 2.423 wordcount --update 2.391 wordcount --update --without-rowid 0.375 wordcount --delete 0.328 wordcount --delete --without-rowid 0.372 wordcount --query 0.328 wordcount --query --without-rowid 24.027 wordcount --all Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: sqlite-users on behalf of Richard Hipp Sent: Saturday, April 21, 2018 3:49:08 AM To: SQLite mailing list Subject: Re: [sqlite] UPSERT available in pre-release Please try again with the latest pre-release snapshot. On 4/19/18, Quan Yong Zhai wrote: > Dear Richard, > > I modified the wordcount.c in SQLite/test directory, to use the new upsert > command: > >INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE > SET cnt=cnt+1 > > Before: > >wordcount --all :memory: sqlite3.c > > 2.406 wordcount --insert > > 2.296 wordcount --insert --without-rowid > > After: > > wordcount --all :memory: sqlite3.c > > 1.701 wordcount --insert > > 3.547 wordcount --insert --without-rowid > > > > As you can see, it’s very strangely ,in the table with rowid, the upsert > improved a lot, but in the table without rowidd, it’s slower than the origin > sql. > > > > > > Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows > 10 > > > > > From: sqlite-users on behalf > of Richard Hipp > Sent: Thursday, April 19, 2018 6:29:55 PM > To: General Discussion of SQLite Database > Subject: [sqlite] UPSERT available in pre-release > > The latest pre-release snapshot [1] contains support for UPSERT > following the PostgreSQL syntax. The documentation is still pending. > Nevertheless, early feedback is welcomed. You can respond either to > this mailing list, or directly to me. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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 > -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] UPSERT available in pre-release
I full agree with Petite Abeille. This upsert is quite limited, we can only insert one row on conflict update one row. Even so it is a big improvement versus the insert or replace, this is far from the merge from the SQL standard where we can insert or update multiple rows in one query. I am already super mega happy to think about using this new upsert for sure, but could you implement the merge from the SQL standard? Best regards, Sylvain ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
Please try again with the latest pre-release snapshot. On 4/19/18, Quan Yong Zhai wrote: > Dear Richard, > > I modified the wordcount.c in SQLite/test directory, to use the new upsert > command: > >INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE > SET cnt=cnt+1 > > Before: > >wordcount --all :memory: sqlite3.c > > 2.406 wordcount --insert > > 2.296 wordcount --insert --without-rowid > > After: > > wordcount --all :memory: sqlite3.c > > 1.701 wordcount --insert > > 3.547 wordcount --insert --without-rowid > > > > As you can see, it’s very strangely ,in the table with rowid, the upsert > improved a lot, but in the table without rowidd, it’s slower than the origin > sql. > > > > > > Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows > 10 > > > > > From: sqlite-users on behalf > of Richard Hipp > Sent: Thursday, April 19, 2018 6:29:55 PM > To: General Discussion of SQLite Database > Subject: [sqlite] UPSERT available in pre-release > > The latest pre-release snapshot [1] contains support for UPSERT > following the PostgreSQL syntax. The documentation is still pending. > Nevertheless, early feedback is welcomed. You can respond either to > this mailing list, or directly to me. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
On 4/19/18, Quan Yong Zhai wrote: > I modified the wordcount.c in SQLite/test directory, to use the new upsert > > Before: >wordcount --all :memory: sqlite3.c > 2.406 wordcount --insert > 2.296 wordcount --insert --without-rowid > > After: > wordcount --all :memory: sqlite3.c > 1.701 wordcount --insert > 3.547 wordcount --insert --without-rowid > > As you can see, it’s very strangely ,in the table with rowid, the upsert > improved a lot, but in the table without rowidd, it’s slower than the origin > sql. That's a good testing idea. Thank you. I will make a similar change and investigate the cause of the slowdown, and hopefully fix the problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
Dear Richard, I modified the wordcount.c in SQLite/test directory, to use the new upsert command: INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET cnt=cnt+1 Before: wordcount --all :memory: sqlite3.c 2.406 wordcount --insert 2.296 wordcount --insert --without-rowid After: wordcount --all :memory: sqlite3.c 1.701 wordcount --insert 3.547 wordcount --insert --without-rowid As you can see, it’s very strangely ,in the table with rowid, the upsert improved a lot, but in the table without rowidd, it’s slower than the origin sql. Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: sqlite-users on behalf of Richard Hipp Sent: Thursday, April 19, 2018 6:29:55 PM To: General Discussion of SQLite Database Subject: [sqlite] UPSERT available in pre-release The latest pre-release snapshot [1] contains support for UPSERT following the PostgreSQL syntax. The documentation is still pending. Nevertheless, early feedback is welcomed. You can respond either to this mailing list, or directly to me. -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] UPSERT available in pre-release
> On Apr 19, 2018, at 1:06 PM, Richard Hipp wrote: > > We are open to adding MERGE INTO at some point in the future. Excellent! > But the UPSERT syntax is both easier to understand Debatable. > and easier to implement, Possibly. > and we prefer to follow PostgreSQL syntax whenever possible. See > https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for > PostgreSQL's rationale for rejecting MERGE. Let’s agree to disagree on that long running opinion piece. MERGE, as per SQL:2003 & SQL:2008 & co. is the way to go. The situation remind me of the introduction of recursive common table expression (CTE) in SQLite, which at first you wanted to implement solely in terms of Oracle’s 'CONNECT BY’ syntax, but ultimately saw the benefit of embracing the full-fledged CTE syntax instead. And I’m personally very grateful for that thought process which gifted SQLite a kickass CTE implementation. Thanks for that! Hopefully, the UPSERT vs. MERGE conversation will move that way as well: MERGE FTW! :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
On 4/19/18, Petite Abeille wrote: > > >> On Apr 19, 2018, at 12:29 PM, Richard Hipp wrote: >> >> The latest pre-release snapshot [1] > > Link missing? [1] https://sqlite.org/download.html > >> contains support for UPSERT >> following the PostgreSQL syntax. >> The documentation is still pending. >> Nevertheless, early feedback is welcomed. You can respond either to >> this mailing list, or directly to me. > > Postgres UPSERT?!? > > Wouldn’t a standard ANSI MERGE be more appropriate? > We are open to adding MERGE INTO at some point in the future. But the UPSERT syntax is both easier to understand and easier to implement, and we prefer to follow PostgreSQL syntax whenever possible. See https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for PostgreSQL's rationale for rejecting MERGE. MySQL also has UPSERT but no MERGE. The MySQL UPSERT syntax is similar, but omits the constraint-target clause following the ON CONFLICT. So if there are multiple uniqueness constraints, you never know which one will receive the UPSERT in MySQL. That seemed problematic, so we dropped support for the MySQL syntax during development. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the least useful because it's very limited: It can only do a check against a constraint, and the cost of evaluating that constraint has to be carried by all other statements which mutate the table. Oracle/Teradata MERGE is a far more useful semantics because it's defined more like a self-join, where the constraint is specified in the statement, not the DBMS. On 04/19/2018 11:29 AM, Richard Hipp wrote: The latest pre-release snapshot [1] contains support for UPSERT following the PostgreSQL syntax. The documentation is still pending. Nevertheless, early feedback is welcomed. You can respond either to this mailing list, or directly to me. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
> On Apr 19, 2018, at 12:29 PM, Richard Hipp wrote: > > The latest pre-release snapshot [1] Link missing? > contains support for UPSERT > following the PostgreSQL syntax. > The documentation is still pending. > Nevertheless, early feedback is welcomed. You can respond either to > this mailing list, or directly to me. Postgres UPSERT?!? Wouldn’t a standard ANSI MERGE be more appropriate? https://en.wikipedia.org/wiki/Merge_(SQL) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT available in pre-release
The latest pre-release snapshot [1] contains support for UPSERT following the PostgreSQL syntax. The documentation is still pending. Nevertheless, early feedback is welcomed. You can respond either to this mailing list, or directly to me. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users