On Wed, Jan 5, 2011 at 12:42 PM, Peter <[email protected]>wrote:
> I have a Python web application that I am converting to use SQLite3 for > local deployment instead of PostgreSQL to make it simpler to install. > > Some queries run up to 50 times slower using SQLite. We can probably help you better if will post your entire schema, including all table, index, view, and trigger definitions, in addition to the query that is giving your trouble. Even better would be for you to run the sqlite3_analyzer.exe utility over your database and send that result too, so that we can get an idea of how much data is in your database and how it is distributed. > They are all doing > similar things - extracting the latest record from a change log table > using a query of the form: > > SELECT latest.regn_no, latest.tag_prefix, latest.indiv_no, > test.num_indiv_no > FROM ear_tag latest > WHERE NOT EXISTS (SELECT 'x' > FROM ear_tag even_later > WHERE latest.regn_no = even_later.regn_no > AND latest.date_assigned < even_later.date_assigned) > > I have a view sheep_progeny_data: > SELECT s.regn_no, s.text_dob, s.sort_dob, s.flock_book_vol, > s.breeder_person_id, s.regn_person_id, > s.originating_flock, s.registering_flock, s.sex, s.sheep_name, > s.sire_no, s.dam_no, s.register_code, > s.colour, s.horns, s.litter_size, s.registration_date, of.flock_name as > org_flock_name, i.result_code, p.allele_1, p.allele_2 > FROM sheep s JOIN flock of ON s.originating_flock = of.flock_no > LEFT JOIN current_inspection i ON s.regn_no = i.regn_no > LEFT JOIN current_prp p ON s.regn_no = p.regn_no > > Both the last two 'tables' are views of the form I gave above. I'm using > the same indexes for both SQLite and PostgreSQL. > > The sheep_progeny_data view is used to get the data for one sheep. It > takes about 100ms to run on SQLite and 2ms on PostgreSQL when I time the > Python code. > > You can see the query in action at > http://www.ppdb.org.uk/sss/sheep/progeny/020330. > > The database is here: http://somborneshetlands.co.uk/things/sss-mini.zip > > The Python code is identical except for the database adapter - psycopg2 > and sqlite3. But I don't think it's anything to do with Python since I > get the same order of difference using PGAdmin3 to access PostgreSQL and > SQLiteman or SQLite Manager to access SQLite. > > Any help welcomed. > > Pete > > -- > Peter Hardman > > 'For every complex problem there is a solution that is simple, > straightforward - and wrong' > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
