Hi, I guess, if you could sort your input data by col1 and col2; going thru the list and insert only when col1 and col2 are different from previous row, should speed up the loading too.
Regards, Radzi. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, 10 January, 2012 6:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] insert if record not exists without primary key 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
