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. 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
