Now that I have SQLite compiled on HP, I am starting to test performance. So far it's pretty disappointing though. I am comparing performance of SQLite versus an in-house directory access system. I have the same table structure and data for each of them. The code reads some data from an input file and searches the database for that row, then finds all rows from other tables associated with the first row. On Windows, with a cold cache SQLite is a bit slower than the inhouse system, but after an initial select it runs twice as fast. On HP our inhouse system can run through 1000 rows in input in 4 seconds on average. SQLite is taking about 30 seconds with the same input to find the same results. 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); I also reordered on these indexes to create the clustered index. On Windows I had set the page_size PRAGMA to 4096 before creating the tables, but I think on HP they are 1024 so that wouldn't help? My program uses the following queries to find the info and binds the appropriate data from the input file. char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
