On Sun, 29 Mar 2009 15:19:00 -0400 (EDT), mrobi...@cs.fiu.edu wrote: >Hi, > >I am new with sqlite, and I create a program that reads several mllion >records and puts them into a sqlite db using. > >The table has one column ONLY indexed and unique, but it takes many hours. > >Is it necessary to pre-allocate the space, or is the anything that I can >do to reduce the time it takes. > >this is how I create the db, table and index. > >void openSqliteFile() >{ > rc = sqlite3_open(genome_probesDB, &db); //if it !exist creates it > > if (rc == SQLITE_OK) { > printf("RC=%d database=%s was opened\n", rc, genome_probesDB ); > } > else { > printf("RC=%d database=%s COULD NOT OPEN\n", rc, genome_probesDB ); > } > > rc = sqlite3_exec(db, "CREATE TABLE probes (probe STRING unique);", >NULL, NULL, &errmsg); > if (rc == SQLITE_OK) { > printf("RC=%d table probes with field probe was created\n", rc ); > } > else { > printf("RC=%d table %s already exists, so it was NOT created\n", >rc, genome_probesDB ); > } > > rc = sqlite3_exec(db, "CREATE INDEX probe on probes (probe);", NULL, >NULL, &errmsg); > if (rc == SQLITE_OK) { > printf("RC=%d INDEX probe on table probes for field probe was >created\n", rc ); > } > else { > printf("RC=%d INDEX probe on table %s already exists, so it was NOT >created\n", rc, genome_probesDB ); > } > > >}//end void openSqliteFile() > > >and this is how I add the data: > char *zSQL = sqlite3_mprintf("INSERT INTO probes VALUES(%Q)", probe); > > sqlite3_exec(db, zSQL, 0, 0, 0); > sqlite3_free(zSQL);
Two common optimizations: 1) Wrap the INSERT statements in a transaction while not EOF on input file BEGIN loop 10000 times or EOF read input record INSERT endloop COMMIT endwhile 2) Sort your input file on the PRIMARY KEY or on some other INDEX >Thanks very much > >Michael -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users