Re: [sqlite] Query optimization: Checking for existence before performing action
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
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
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
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
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
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
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
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
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