You haven't said what kind of machine you're running on.
I did this test using 9,000,000 records and got 40,000 inserts per second.
sqlite3 test.db <x1.sql
time sqlite3 test.db <x2.sql
It took 225 seconds on my 8-core 2.5Ghz Xeon machine.
It created a 797Mb database.
x1.sql:
CREATE TABLE mydata
(
itno VARCHAR(20),
lcno VARCHAR(20),
srno VARCHAR(10),
ran VARCHAR(20),
pp INTEGER,
cdate VARCHAR(20),
PRIMARY KEY (itno DESC, lcno ASC)
);
x2.sql:
begin;
insert into mydata VALUES(0,000000000000000056888,'AABBCC',4.9999999,157, '2009/
9/10 19:55:50');
insert into mydata VALUES(1,000000000000000056888,'AABBCC',4.9999999,157, '2009/
9/10 19:55:50');
insert into mydata VALUES(2,000000000000000056888,'AABBCC',4.9999999,157, '2009/
9/10 19:55:50');
insert into mydata VALUES(3,000000000000000056888,'AABBCC',4.9999999,157, '2009/
9/10 19:55:50');
insert into mydata VALUES(4,000000000000000056888,'AABBCC',4.9999999,157, '2009/
9/10 19:55:50');
.....
commit;
Here's the code that created x2.sql
main()
{
int i;
char buf[4096];
puts("begin");
for(i=0;i<9000000;i++) {
sprintf(buf,"insert into mydata
VALUES(%d,000000000000000056888,'AABBCC',4.9999999,157, '2009/9/10
19:55:50');",i);
puts(buf);
}
puts("commit");
}
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
________________________________
From: [email protected] on behalf of ??
Sent: Tue 5/11/2010 11:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert large data question ??
Thanks Kishor ,I will note it !!
I already used transaction to doing this job.
I tried to remove all of index ,this time the job used about 31600 seconds
ps. I had use "PRAGMA synchronous=OFF" in front of my transaction.
someone can help me do this job more faster ??
thank everybody
2010/5/11 P Kishor <[email protected]>:
> On Tue, May 11, 2010 at 12:47 AM, ?? <[email protected]> wrote:
>> Dear
>>
>> I have about 9 million data insert string need to insert into an table ,each
>> row data is unique
>>
>> this is a sample:
>> insert into mydata
>> VALUES(38824801,000000000000000056888,'AABBCC',4.9999999,157,'2009/9/10
>> 19:55:50');
>>
>> this is my schema:
>> table|mydata|mydata|2|CREATE TABLE mydata
>> (
>> itno VARCHAR(20),
>> lcno VARCHAR(20),
>> srno VARCHAR(10),
>> ran VARCHAR(20),
>> pp INTEGER,
>> cdate VARCHAR(20),
>> PRIMARY KEY (itno DESC, lcno ASC)
>> )
> ..
>> but i have question about performance,everytime doing the job takes about
>> 63000 seconds
>
> Use transactions. But, do you also realize that most of your columns
> are defined as VARCHAR, but you are inserting stuff that doesn't look
> like TEXT. You will be/should be surprised by the results. From the
> example above,
>
>> itno VARCHAR(20),
>> lcno VARCHAR(20),
>> srno VARCHAR(10),
>> ran VARCHAR(20),
>> pp INTEGER,
>> cdate VARCHAR(20),
>
> ltno VARCHAR(20): 38824801 <-- if it had leading zeroes, they would vanish
> lcno VARCHAR(10): 000000000000000056888 <-- will become 56888
> srno VARCHAR(10): 'AABBCC' <-- inserted correctly
> ran VARCHAR(20): 4.9999999 <-- are you expecting this to remain a REAL?
> pp INTEGER: 157 <-- inserted correctly
> cdate VARCHAR: '2009/9/10 19:55:50' <-- inserted correctly
>
> enclose your VARCHARs in single quotes.
>
> --
> Puneet Kishor http://www.punkish.org <http://www.punkish.org/>
> Carbon Model http://carbonmodel.org <http://carbonmodel.org/>
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> <http://www.osgeo.org/>
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> <http://www.nelson.wisc.edu/>
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
