Try increasing your cache size so your whole database fits inside. I'm sure that's what postgres is doing. pragma cache_size=100000; Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: [email protected] on behalf of Peter Sent: Wed 1/5/2011 11:42 AM To: [email protected] Subject: EXTERNAL:[sqlite] Query slow on SQLite 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
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
