Re: [sqlite] Insert large data question ??

Black, Michael (IS) Wed, 12 May 2010 05:18:15 -0700

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: sqlite-users-boun...@sqlite.org 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 <punk.k...@gmail.com>:
> On Tue, May 11, 2010 at 12:47 AM, ?? <ler...@gmail.com> 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
> 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

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert large data question ??

Reply via email to