You are measuring the speed of the respective machines. Benchmark each one to get relative performance.
Andrea Connell wrote: > > Sorry somehow I sent that before I was quite finished. I'm just > wondering if there is anything else I should try. About 30,000 rows are > found in the end, and it can do this on Windows in less than a second. > I'm convinced it shouldn't take 30 seconds on HP. I know the OS's > caching method will make a difference, but should it be this dramatic? > > Thanks, > Andrea > > -----Original Message----- > From: Andrea Connell > Sent: Friday, June 20, 2008 11:23 AM > To: sqlite-users@sqlite.org > Subject: Performance on HP > > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
