Simon, Thank you.
>>create table if not exists emp(id integer primary key >>autoincrement, col1 text, col2 text, unique (col1, col2)); I noticed, with first approach, huge performance hit when database grows. for ex: database has 500,000 records. now insert 25,000 within a transaction. It takes lot of time when compared to insert 25,000 records with empty database. approximately 15 times slower. because of unique(col1, col2). I did with transactions for every 25,000 records. Any suggestions? Durga. On Tue, Jan 10, 2012 at 1:00 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 10 Jan 2012, at 7:23am, Durga D wrote: > > > create table if not exists emp(id integer primary key autoincrement, > > col1 text, col2 text, unique (col1, col2)); > > > > here, col1 and col2 should be unique. I tried to insert 10000000 records > > with unique(col1, col2). It's very slow. So, I choosed id as primary key > > (surrogate key) and without unique key. > > > > create table if not exists emp(id integer primary key autoincrement, col1 > > text, col2 text); > > > > I want to insert a record if not exists. How? > > You did it correctly the first time. That is the correct way to prevent > duplicates. > > Your test is unrealistic and will not reflect how fast the database will > be in real life (are you really going to have your application insert > 10,000,000 rows often ?) but if you really do want to do this, put all your > insert commands into a transaction: > > BEGIN TRANSACTION; > insert ...; > insert ...; > insert ...; > ...; > END TRANSACTION; > > 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
