sqlite – (INSERT OR REPLACE) vs (SELECT followed by INSERT)

[Michael Alexander2]

If data needs to be added to an sqlite table 10% of the time, is it more efficient to use “INSERT OR REPLACE” or do a SELECT first followed by INSERT if not found?  With some versions of SQL, SELECT won’t lock the table, but I’m not sure about sqlite.  The documentation suggests that opening the database locks it.  There is only one column, so there won’t be any updates.

Code in TCL proc:

sqlite db $dbName

set exist_flag [db exists {SELECT 1 FROM transplant_t WHERE SHC_Num = $shcnum}]

if {! $exist_flag} {

  db eval {INSERT INTO transplant_t VALUES ($shcnum)}

}

db close $dbName

Topic

[Max Drown (Infor)]

I’ve haven’t had any problems using INSERT. I try to keep my sqlite code in one process so locking won’t be an issue.

-- Max Drown (Infor)

[Author]

Replies