On 2016/06/30 12:00 PM, Olivier Mascia wrote:
Besides, if you ever have to write a scripts for the command line tool, you're
stuck trying to emulate that semantic. Or can we check by script the outcome of
the UPDATE?
There is no way to catch the outcome in the CLI that I know of, but that
doesn't matter. if you are writing scripts for the CLi and not
programming it, you can't possibly have SPEED as a paramount
consideration, and if that's the case, the simple usual upserts in the
CLI would work just dandy (and the order doesn't matter much for
lookups, but it is very slightly more efficient to do the Update first
as sometimes there's nothing to update - if the update is done second,
there's always something to update.
Here is a performance measurement of a typical script that would run
just fine in the CLi merrily doing Upserts wihout needing to check any
operation result (i.e the slowest possible way to do it).
It's run in SQLitespeed (but will work perfectly in the CLI too) so I
can measure the Virtual Machine operations count and compare with the
same script but without any of the unnecessary steps that makes upserts
(this second one has half the SQL operations and will even be much more
efficient than a MERGE statement).
Note the results in VM Steps measured in both cases. To my mind, the
difference is not worth fretting over if you make scripts for the CLI.
(Note: The time improvement has more to do with caching than efficiency,
but the VM steps don't lie).
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 10 Parameter Count: 0
-- 2016-06-30 13:47:04.423 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);
INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');
SELECT * FROM t;
-- ID | A | B
-- ------------ | ------- | -------
-- 1 | John | Smith
-- 2 | Jerry | Jones
-- 3 | James | Smith
-- 4 | Jimmy | Jones
UPDATE t SET A='Jenny', B='Smith' WHERE ID=2; -- UPSERT Type
1(best)
INSERT OR IGNORE INTO t(ID,A,B) VALUES (2,'Jenny','Smith');
INSERT OR IGNORE INTO t(ID,A,B) VALUES (3,'Jenna','Jones');-- UPSERT Type 2
UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;
UPDATE t SET A='J.K.', B='Johnson' WHERE ID=99; -- UPSERT Type 1 again
INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');
SELECT * FROM t;
-- ID | A | B
-- ------------ | ------- | ---------
-- 1 | John | Smith
-- 2 | Jenna | Jones
-- 3 | James | Smith
-- 4 | Jimmy | Jones
-- 99 | J.K. | Johnson
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.069s
-- Total Script Query Time: 0d 00h 00m and
00.037s
-- Total Database Rows Changed: 7
-- Total Virtual-Machine Steps: 233
-- Last executed Item Index: 10
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 7 Parameter Count: 0
-- 2016-06-30 13:53:38.403 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);
INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');
SELECT * FROM t;
-- ID | A | B
-- ------------ | ------- | -------
-- 1 | John | Smith
-- 2 | Jerry | Jones
-- 3 | James | Smith
-- 4 | Jimmy | Jones
UPDATE t SET A='Jenny', B='Smith' WHERE ID=2;
UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;
INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');
SELECT * FROM t;
-- ID | A | B
-- ------------ | ------- | ---------
-- 1 | John | Smith
-- 2 | Jenna | Jones
-- 3 | James | Smith
-- 4 | Jimmy | Jones
-- 99 | J.K. | Johnson
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.031s
-- Total Script Query Time: -- --- --- ---
--.----
-- Total Database Rows Changed: 7
-- Total Virtual-Machine Steps: 194
-- Last executed Item Index: 7
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
