> 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 ??
have you tried increasing the page cache ? eg. --> PRAGMA cache_size = 400000; occasionally it helps when dealing with large tables.. Marcus > > 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 >> Carbon Model http://carbonmodel.org >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor >> Nelson Institute, UW-Madison 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
