Have you tried compiling with the profiler and seeing where the time is being spent?
Andrea Connell wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
