Thanks very much.
I will try different values for the loops until a get an optimal number,
and I will try the "PRAGMA cache_size = <#pages>". command.
question:
When you say
> 2) Sort your input file on the PRIMARY KEY
> or on some other INDEX
I thought that while sqlite inserts the data it is creating the indexes
therefore sorting the data by way of the index, is this corret?
I have decared one column ONLY, unique and indexed.
Thanks again,
Michael
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
