Are there any other processes or threads trying to open your db file while
you run your tests?
On Mon, Jun 23, 2008 at 9:48 AM, Andrea Connell <[EMAIL PROTECTED]>
wrote:
>
>
> >> The program took 47 seconds to run, but the results only account for
> >> .39 seconds
> >
> > Most likely all the time is being spent in IO related system calls
> > - read(), write() and fsync().
> >
> > Dan.
>
>
> Thanks for the idea Dan. How can I confirm this or try reducing the time
> spent? I use the same method for reading my input file when I run both
> SQLite and our in house system, and the other way only takes 4 seconds
> total so I don't think it could be from that. Also, when I run our in
> house system and use the profiler the time spent adds up to 100%
>
> So this must be something within SQLite. I am using a transaction for my
> queries. How can I find all of that missing time?
>
>
> If anybody is interested, here is my main chunk of code. ReadLine()
> parses the input file and fills the required variables. This method is
> shared for both database systems (SQLite and ours). ReadSQLiteComponent
> just calls one of the sqlite3_column functions based on the type of the
> field, and a similar method is used for our system.
>
>
> std::ifstream inf(argv[1]);
>
> sqlite3 *db;
> sqlite3_stmt *stmt;
> sqlite3_stmt *stmt2;
> sqlite3_stmt *stmt3;
> int rc = sqlite3_open(argv[3], &db);
> if( rc )
> {
> printf("Can't open database: %s\n",
> sqlite3_errmsg(db));
> sqlite3_close(db);
> return -1;
> }
> sqlite3_prepare(db,"BEGIN TRANSACTION;", 100, &stmt,0);
> sqlite3_step(stmt);
>
> char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ?
> AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ?
> ;";
> int p = sqlite3_prepare_v2(db,qry,1000,&stmt,0);
> char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ?
> AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;";
> int p2 = sqlite3_prepare_v2(db,qry2,1000,&stmt2,0);
> char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ?
> ;";
> int p3 = sqlite3_prepare_v2(db,qry3,1000,&stmt3,0);
> if ( p || p2 || p3 )
> {
> printf("Can't create prepared statement: %s\n",
> sqlite3_errmsg(db));
> sqlite3_close(db);
> return -1;
> }
>
> while (ReadLine(inf))
> {
> sqlite3_bind_text(stmt, 1, cntryid, -1,
> SQLITE_TRANSIENT);
> sqlite3_bind_int(stmt, 2, searcharea);
> sqlite3_bind_text(stmt, 3, addrtype, -1,
> SQLITE_TRANSIENT);
> int len = strlen(phnkey);
> phnkey[len] = '%';
> phnkey[len+1] = '\0';
> sqlite3_bind_text(stmt, 4, phnkey, -1,
> SQLITE_TRANSIENT);
>
> while(sqlite3_step(stmt)==SQLITE_ROW)
> {
> for(int i=0; i<numFields[0]; i++)
> {
> ReadSQLiteComponent(0,i, stmt);
> if (i==51) //LEVEL1.RECORDKEY
>
> sqlite3_bind_int(stmt2,1,sqlite3_column_int(stmt, i));
> }
> sqlite3_bind_text(stmt2,2,prmlow, -1,
> SQLITE_TRANSIENT);
> sqlite3_bind_text(stmt2,3,prmhigh, -1,
> SQLITE_TRANSIENT);
> while(sqlite3_step(stmt2)==SQLITE_ROW)
> {
> for(int i=0; i<numFields[1]+1;
> i++)
> {
> ReadSQLiteComponent(1,i,
> stmt2);
> if (i==27)
> //LEVEL2.RECORDKEY
>
> sqlite3_bind_int(stmt3,1,sqlite3_column_int(stmt2, i));
> }
>
> while(sqlite3_step(stmt3)==SQLITE_ROW)
> {
> for(int i=0;
> i<numFields[2]+1; i++)
>
> ReadSQLiteComponent(2,i, stmt3);
> q++;
> }
> sqlite3_reset(stmt3);
> }
> sqlite3_reset(stmt2);
> }
> sqlite3_reset(stmt);
> }
>
> sqlite3_prepare(db,"END TRANSACTION;", 100, &stmt,0);
> sqlite3_step(stmt);
>
> sqlite3_finalize(stmt);
> sqlite3_finalize(stmt2);
> sqlite3_finalize(stmt3);
> sqlite3_close(db);
>
>
> _______________________________________________
> 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
