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

Reply via email to