On Thu, Dec 1, 2011 at 7:42 AM, Peter <pe...@somborneshetlands.co.uk> wrote:
> Hi, > > I have a problem with one of my queries which take 2 orders of magnitude > more on Sqlite3 (3.7.9) compared with the identical query on PostgreSQL > (8.4). Times are 2270 ms on Sqlite3 and around 17ms on PG. > Please try using the patch at http://www.sqlite.org/src/ci/3d4b4f4fb7?sbs=0and let me know if that solves your problem. > > The difference seems to that the Sqlite is not optimising a subquery by > using an index in the way that PG does. > > The query: > > SELECT * FROM new_current_flock_2 WHERE regn_no = '030235' > (any regn no in the sheep table can be used) > > new_current_flock_2 is a view: > > CREATE VIEW new_current_flock_2 AS > SELECT latest.regn_no, latest.flock_no, latest.transfer_date, > latest.text_xfer_date, latest.transfer_reason > FROM new_sheep_xfer_history_2 latest WHERE NOT EXISTS > ( SELECT 'x' FROM new_sheep_xfer_history_2 even_later > WHERE latest.regn_no = even_later.regn_no AND > latest.transfer_date < even_later.transfer_date) > > new_sheep_xfer_history_2 is another view: > > CREATE VIEW new_sheep_xfer_history_2 AS > SELECT sf.regn_no, sf.flock_no, f.flock_name, sf.transfer_date, > sf.text_xfer_date, sf.transfer_reason > FROM sheep_flock sf JOIN flock f ON sf.flock_no = f.flock_no > UNION ALL > SELECT s.regn_no, s.registering_flock, f.flock_name, > s.registration_date || '02:00:00', > s.registration_date, CAST('Registration' AS VARCHAR(16)) as transfer_reason > FROM sheep s JOIN flock f ON s.registering_flock = f.flock_no > UNION ALL > SELECT s.regn_no, s.originating_flock, f.flock_name, > s.sort_dob || '00:00:00', > s.text_dob, CAST('Birth' AS VARCHAR(16)) as transfer_reason > FROM sheep s JOIN flock f ON s.originating_flock = f.flock_no > > As far as I can see Sqlite3 is only applying the where clause after > computing the whole of new_current_flock_2, including > new_sheep_xfer_history_2 (twice) whereas PG is applying the 'where' clause > to each of the three subqueries in new_sheep_xfer_history_2 as it computes > that table. But I might be wrong ;) > > The sqlite database is at http://www.somborneshetlands.** > co.uk/things/main.tar.gz<http://www.somborneshetlands.co.uk/things/main.tar.gz> > > I _could_ restructure my database to avoid the second view, but I'd much > rather not as it's a pretty major change. > > Does anyone have any ideas for optimisations on the current schema? > > TIA > -- > > Peter Hardman > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
