Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Richard Hipp <d...@sqlite.org>
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 <q...@msn.com> 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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Richard Hipp <d...@sqlite.org>
> 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

[Sylvain Pointeau]

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

[Richard Hipp]

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <q...@msn.com> 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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Richard Hipp <d...@sqlite.org>
> 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

[Richard Hipp]

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

[Quan Yong Zhai]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Richard Hipp <d...@sqlite.org>
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

[Petite Abeille]

> 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

[Richard Hipp]

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

[Shevek]

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

[Petite Abeille]

> 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

[Richard Hipp]

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