SQLite and Javascript : Checking for existence of data before inserting OR letting SQLite throw an exception - Stack Overflow

admin管理员组

文章数量:1434371

My primary question is which approach is faster.

Some briefing

I'm developing an application using Mozilla.

I have this one module where I capture some data and store it in database. The data es in intermittently. No duplicates are to be stored. For discussion sake we can assume a table with just one column, and let's name that column is named 'keyword'. So if we get a keyword that's already in database we do not store it again. And yes, we have set this column as PRIMARY KEY and made it UNIQUE. :)

The query I have is:

1) Before I insert this new data into the database, shall I do a DB call and check if the keyword exists or not; if it doesn't exists put it into DB? Something like:

function insert_keyword(keyword)
{
   if(!SQL.exists(keyword))
   {
       SQL.insert(keyword);
   } 
}

OR

2) I just insert and let the database handle the condition, effectively letting it throw an exception, which I catch.

function insert_keyword(keyword)
{
    try {
       SQL.insert(keyword);
    }
    catch (e)
    {
       // keyword exists!
    }
}

I know catching an exception and not doing anything is bad! So I'm not looking for what is good and what is bad :) . What I want to understand is which approach would be the fastest. (Specifically in relation to Mozilla, JavaScript and SQLite but general ments are weled!)

Sidenotes: The SQL. syntax I've used is just for illustration purpose. You may assume that I create SQL statements, execute them and fetch the result, or assume it is a JavaScript library call which does all the dirty work.

This question is a bit like this one:

Should I check for DB constraints in code or should I catch exceptions thrown by DB

But I want to understand the performance related differences specifically, as the application I'm working on needs to be as fast as possible (which application doesn't? ;) )

My primary question is which approach is faster.

Some briefing

I'm developing an application using Mozilla.

I have this one module where I capture some data and store it in database. The data es in intermittently. No duplicates are to be stored. For discussion sake we can assume a table with just one column, and let's name that column is named 'keyword'. So if we get a keyword that's already in database we do not store it again. And yes, we have set this column as PRIMARY KEY and made it UNIQUE. :)

The query I have is:

1) Before I insert this new data into the database, shall I do a DB call and check if the keyword exists or not; if it doesn't exists put it into DB? Something like:

function insert_keyword(keyword)
{
   if(!SQL.exists(keyword))
   {
       SQL.insert(keyword);
   } 
}

OR

2) I just insert and let the database handle the condition, effectively letting it throw an exception, which I catch.

function insert_keyword(keyword)
{
    try {
       SQL.insert(keyword);
    }
    catch (e)
    {
       // keyword exists!
    }
}

I know catching an exception and not doing anything is bad! So I'm not looking for what is good and what is bad :) . What I want to understand is which approach would be the fastest. (Specifically in relation to Mozilla, JavaScript and SQLite but general ments are weled!)

Sidenotes: The SQL. syntax I've used is just for illustration purpose. You may assume that I create SQL statements, execute them and fetch the result, or assume it is a JavaScript library call which does all the dirty work.

This question is a bit like this one:

Should I check for DB constraints in code or should I catch exceptions thrown by DB

But I want to understand the performance related differences specifically, as the application I'm working on needs to be as fast as possible (which application doesn't? ;) )

• javascript
• exception
• sqlite
• mozilla

Share Improve this question Follow edited May 23, 2017 at 11:46 CommunityBot 111 silver badge asked Apr 6, 2009 at 8:51 xk0derxk0der 3,69044 gold badges2929 silver badges3636 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 6

Just tell your database to ignore duplicates (works only for columns that enforce unique values):

INSERT OR IGNORE INTO table(keyword) VALUES("someWord");

or create a column, that ignores duplicates.

CREATE TABLE someTable(keyword PRIMARY KEY ON CONFLICT IGNORE);

Instead of ignore there are also those conflict clauses:

ROLLBACK
ABORT
FAIL
IGNORE
REPLACE

For more information, read the page about conflict clauses of SQLite and the documentation of the INSERT statement.

---

As to if it's faster using exceptions or checking for the existance of values: Exceptions are expensive when raised and zero-cost when not raised. This means if you expect to have many duplicates use the check and if only a small number of keywords are duplicates use exceptions. After all, exceptions should be exceptional.

Not sure about the specifics of the mozilla/javascript part of this question, but there is a third option 'insert or replace'. It acplishes what you want with no duplicates and gets rid of the need to check in code if the row already exists.

本文标签：