I've been thinking that I am experiencing something similar, but I have no
solid facts nor am I good enough to really figure it out. I have a view
using a view. It feels slow. I'm using the second view to relieve my
program from having to dynamically construct multiple times a union of 1 to
x tables. The process felt much faster with the unions than it does now
with the view.
Right now I am in development and unit testing. Things are going fine with
my test data, which is only a couple 100,000 rows. I'll revisit this issue
when I get to stress testing, which will involve 7 to 11 million rows. If
I find the application is too slow in these areas, I will replace the view
with the constructed unions and see what happens then.
I just wanted to let you know that you are not alone, and are probably
sane. It may be nothing, or maybe it is something.
dvn
On Thu, Dec 1, 2011 at 6: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.
>
> 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>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
