I'm still struggling with this issue. I've tried compiling on Solaris and get similar results. On Windows SQLite is twice as fast as our in-house database system, but on HP and now Solaris it is over 6 times slower than ours. There must be something I am missing here.
I've tried increasing the cache size and the page size but saw no real difference. It was still taking over 30 seconds to find 30,000 rows while our system does it in 4 seconds. The databases have the same schema and the queries are all using the proper index (I checked the explain output). CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , RECORDKEY integer); CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... RECORDKEY integer, PARENT_KEY integer ); CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer ); CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON LEVEL3 (PARENT_KEY); SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ? ; SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?; SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ; Not sure if it will help at all, but once the database is created it will literally never be updated or have another row inserted. Is there any special optimization if we ONLY care about fast selects, and nothing else? Any ideas? It's driving me crazy why SQLite is this much slower on UNIX boxes, while other applications maintain their speed. Thanks from a desperate coder -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrea Connell Sent: Monday, June 23, 2008 11:48 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance on HP >> 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
