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

2 Answers 2

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.