Re: [sqlite] insert if record not exists without primary key

2012-01-10 Thread Mohd Radzi Ibrahim
...@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 n

Re: [sqlite] insert if record not exists without primary key

2012-01-10 Thread Simon Slavin
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.

Re: [sqlite] insert if record not exists without primary key

2012-01-10 Thread Durga D
Yes. I agree. 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. just to know, what might be the reason? On

Re: [sqlite] insert if record not exists without primary key

2012-01-10 Thread Simon Slavin
On 10 Jan 2012, at 8:37am, Durga D wrote: > 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. > >

Re: [sqlite] insert if record not exists without primary key

2012-01-10 Thread Durga D
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

Re: [sqlite] insert if record not exists without primary key

2012-01-09 Thread Simon Slavin
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 1000 records > with unique(col1, col2). It's very slow. So, I choosed

[sqlite] insert if record not exists without primary key

2012-01-09 Thread Durga D
Hi all, I have emp sqlite table: 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 1000 records with unique(col1, col2). It's very slow. So, I choosed id as primary