I understand that the machines will perform differently, but that doesn't explain the differences I am seeing. On Windows SQLite can do 1,000 rows of input in less than a second and our in house system can do it in just over a second. They are fairly comparable at this point. When I increase the rows of input to 10,000 SQLite can do it in 5 seconds and our in house system takes 12. 100,000 rows are done in 54 seconds and 121 seconds, respectively. So from these results you can see that SQLite is about twice as fast as our system on Windows. However, on HP SQLite takes 30 seconds to do 1,000 rows and our system can do them in just 4 seconds. Clearly it is not the machine itself that is 30 times slower. If our system took 60 seconds to perform the queries, it would make sense but that is not at all the case. That is why I am wondering if there is something else in SQLite that might be causing the drastic slow down on HP. Sorry if that wasn't totally clear from my first message.
Any thoughts? Andrea -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Friday, June 20, 2008 12:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance on HP 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
