Try wrapping all the inserts in a transaction:
BEGIN;
INSERT ...
INSERT ...
....
COMMIT;
This should be MUCH faster.
-Steve W
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);
>
>
>
> Thanks very much
>
> Michael
>
> _______________________________________________
> 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
