On 10 Jan 2012, at 10:34am, Durga D wrote:
> What about second approach.
>
> create table if not exists emp (id integer primary key autoincrement, col1
> text, col2 text); //without unique.
>
> I tried with insert or ignore into emp (col1, col2) values ('a', 'b');
>
> I noticed, this is fast.
Here you are not asking SQLite to check for uniqueness every time you do an
INSERT. So you are saving it a lot of work. Of course, the result is a
database which may include a lot of duplicate rows. Assuming you are writing
software, the simplest way to remove these duplicates would be to execute
CREATE INDEX IF NOT EXISTS empCol1Col2 ON emp (col1,col2);
You can then write some software which does
SELECT id,col1,col2 FROM emp ORDER BY col1,col2;
and works its way down the list, deleting all rows where col1 and col2 are the
same as they were for the previous row. (You can perhaps figure out a single
DELETE FROM command which will do all this, but I see no reason it should take
much less time than an efficiently written program.)
Of course this clean-up operation will probably take so much time you'd have
been better off with your first approach.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
