On Sun, 29 Mar 2009 15:19:00 -0400 (EDT),
[email protected] 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
