Re: [sqlite] sqlite WAL mode

[Frank Chang]

Michael D. Black, Thank you for your suggestion use the sqlite 3.7 WAL mode. We 
wrote a small test program to open 2 WAL connections to the main database and 
insert 5.4 million rows into a table. The code is shown below. We wiil add 
sqlite error handling handling code tomorrow.
  The program appears to be running okay but we noticed it took 5 minutes for 
the sqlite3_open corresponding to the DROP TABLE statement to complete. Is 
there anything we can do to speed up the DROP TABLE? Thank you.
 
sprintf(Path,"%s/mdMatchup.dat",ConfigPath);
if (sqlite3_open(Path,&Database)!=SQLITE_OK) {
   return mdMUBatchUpdate::ErrorConfigFile;
}
 
sprintf(Path,"%s/mdMatchup.dat",ConfigPath);
if (sqlite3_open(Path,&Database2)!=SQLITE_OK) {
    return mdMUBatchUpdate::ErrorConfigFile;
}
 
ReturnValue = sqlite3_create_function(Database, "msign", 4, SQLITE_UTF8, NULL,
&cIntersectingGroupCache::msignFunc, NULL, NULL);
ReturnValue = sqlite3_create_function(Database2, "CombineBlob", 3, SQLITE_UTF8, 
NULL,
&cIntersectingGroupCache::CombineBlobFunc, NULL, NULL);
 
strcpy(Command,"PRAGMA journal_mode=wal");
ReturnValue=sqlite3_prepare(Database,Command,-1,&Statement,0);

status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
ReturnValue=sqlite3_prepare(Database2,Command,-1,&Statement2,0);

status = sqlite3_step(Statement2);
status = sqlite3_finalize(Statement2);



 
sprintf(Command,"SELECT COUNT(*) FROM sqlite_master WHERE [Name]=\"KeyFile\"");
ReturnValue=sqlite3_prepare(Database,Command,-1,&Statement,0);

status = sqlite3_step(Statement);

if (status==SQLITE_ROW){
    Count = sqlite3_column_int(Statement,0);
}
status = sqlite3_finalize(Statement);
 
if (Count == 0){
     strcpy(Command,"CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR 
(256), [DupeGroup] INTEGER) ");
     ReturnValue=sqlite3_prepare(Database,Command,-1,&Statement,0);

     status = sqlite3_step(Statement);
     status = sqlite3_finalize(Statement);
}
else {
    strcpy(Command,"DROP TABLE [KeyFile]");
    ReturnValue=sqlite3_prepare(Database,Command,-1,&Statement,0);

    status = sqlite3_step(Statement);
    status = sqlite3_finalize(Statement);
    strcpy(Command,"CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR 
(256), [DupeGroup] INTEGER) ");
    ReturnValue=sqlite3_prepare(Database,Command,-1,&Statement,0);
    status = sqlite3_step(Statement);
    status = sqlite3_finalize(Statement);
}
                                          
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users