>> 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
