Re: [sqlite] Query optimization: Checking for existence before performing action

[James K. Lowden]

On Mon, 29 Jul 2013 13:23:07 +0100
Simon Slavin  wrote:

> INSERT OR IGNORE a new row with the correct 'word' and a confidence
> of 0 
> UPDATE the row with that word to increment the confidence.
...
> If that solution doesn't work for you you might like to try first
> doing
> 
> UPDATE myTable SET ...
> 
> and then looking at the result of
> 
> sqlite3_changes()
> 
> to see whether it is 1 or not.  If it's zero, then you insert a new
> row, with a confidence of 1.

In the general case, if you UPDATE and find zero rows changed, and
then another process inserts the row (with count 1) before your INSERT,
then your INSERT will fail and confidence will remain at 1 when it
should be incremented to 2.  

INSERT OR IGNORE a new row with confidence of 1 
if( 0 == sqlite3_changes() )
UPDATE set confidence = confidence +1

works in the presence of other updaters.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 2 Aug 2013, at 2:09pm, Igor Tandetnik  wrote:

> On 8/2/2013 8:14 AM, Simon Slavin wrote:
>> 
>> On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:
>> 
>>> Hi Simon,
>>> 
>>> the solution might look elegant, but it is probably a lot slower. I did not
>>> check this particular case, but in the past I found triggers to perform
>>> rather badly.
>> 
>> I am puzzled.  My solution does not involve any triggers.
> 
> ... whereas that from Simon Davies does.

I am unpuzzled.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Igor Tandetnik]

On 8/2/2013 8:14 AM, Simon Slavin wrote:


On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:


Hi Simon,

the solution might look elegant, but it is probably a lot slower. I did not
check this particular case, but in the past I found triggers to perform
rather badly.


I am puzzled.  My solution does not involve any triggers.


... whereas that from Simon Davies does.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:

> Hi Simon,
> 
> the solution might look elegant, but it is probably a lot slower. I did not
> check this particular case, but in the past I found triggers to perform
> rather badly.

I am puzzled.  My solution does not involve any triggers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Jan Slodicka]

Hi Simon,

the solution might look elegant, but it is probably a lot slower. I did not
check this particular case, but in the past I found triggers to perform
rather badly.

Regards,
Jan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Query-optimization-Checking-for-existence-before-performing-action-tp70297p70356.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Keith Medcalf]

INSERT OR IGNORE INTO table (word, confidence) VALUES (:word, 
:initialconfidence - :confidenceincrement);
UPDATE table SET confidence=confidence+:confidenceincrement WHERE word=:word;

Still two statements but does not require application "help" and the rowid is 
stable ...

Assuming that :initialconfidence and :confidenceincrement are constant, you 
could put the two statements in an instead of trigger on the table.  Then your 
code would only need to execute a single statement ...


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Navaneeth.K.N
> Sent: Monday, 29 July, 2013 05:58
> To: General Discussion of SQLite Database
> Subject: [sqlite] Query optimization: Checking for existence before
> performing action
> 
> Hello,
> 
> I am trying to optimize the SQL calls that my application makes. I
> have a scenario where words are inserted into a table. Now each word
> will have a column called "confidence". There is a unique primary key
> on "word".
> 
> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
> 
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.
> 
> Any help would be great!
> 
> --
> Thanks
> Navaneeth
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Davies]

On 29 July 2013 12:57, Navaneeth.K.N  wrote:
> Hello,
>
> I am trying to optimize the SQL calls that my application makes. I
> have a scenario where words are inserted into a table. Now each word
> will have a column called "confidence". There is a unique primary key
> on "word".
>
> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
>
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.
>
> Any help would be great!

Something like:

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table words( id text unique, confidence integer default 0 );
sqlite> create trigger words_before_insert before insert on words
   ...> begin update words set confidence=confidence+1 where id=new.id;
   ...> end;
sqlite>
sqlite> select * from words;
sqlite>
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'bill' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite>
sqlite> select * from words;
fred|4
bill|0
joe|1
sqlite>

>
> --
> Thanks
> Navaneeth

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 29 Jul 2013, at 12:57pm, Navaneeth.K.N  wrote:

> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
> 
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.

If you have defined your tables correctly, the rowid would not change, the 
INSERT would just fail.  You could define the 'word' column as UNIQUE, or once 
our table is created create an index which forces the 'word' column to be 
unique.  Then inserting another row with the same 'word' would fail.  Then your 
program to increment a word could would do something like

INSERT OR IGNORE a new row with the correct 'word' and a confidence of 0
UPDATE the row with that word to increment the confidence.

Since the table is set up not to allow duplication, if the row already exists 
the 'INSERT' will fail, but since you used 'INSERT OR IGNORE' your program 
would IGNORE the failure and carry on regardless, incrementing the existing 
confidence.


If that solution doesn't work for you you might like to try first doing

UPDATE myTable SET ...

and then looking at the result of

sqlite3_changes()

to see whether it is 1 or not.  If it's zero, then you insert a new row, with a 
confidence of 1.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query optimization: Checking for existence before performing action

[Navaneeth.K.N]

Hello,

I am trying to optimize the SQL calls that my application makes. I
have a scenario where words are inserted into a table. Now each word
will have a column called "confidence". There is a unique primary key
on "word".

When inserting a word, first I check if the words exists by performing
a "select" query. If it exists, I fire an update query to increment
the confidence for that word.  If word is not available, I fire an
insert query to insert the word.

In both the cases, I can't skip doing two queries. One for checking
existence and second for updating or creating. I am wondering is there
an easy way to solve this by just doing one query? I have tried
"insert or replace", but I can't use that as it changes the rowid's.

Any help would be great!

-- 
Thanks
Navaneeth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users