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
