Hi,
I maintain a personal database on sqlite. It is quite small, with
about 30 tables, and an equal number of views. One of these views is an
inner join of the contents of about 15 of these views, producing a summary
view of my data. The views combined in this summary view contain about
3200 rows each, and the summary view usually runs in about 2 seconds or
less.
Everything was working fine under 3.15. I just use the commandline
tool (sqlite.exe) to interact with my database. I am not a programmer and
don't have a need for programmatic access to this database. I recently
updated to 3.19.3, and now when I select from that summary view, I get no
results - the query just runs for minutes on end, and I eventually lose
patience and kill the process with a ctrl-c.
I have produced an anonymized version of my database and loaded it to
https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg for anybody
to access. If you load this database (named test.db) into the sqlite
command line shell of version 3.15 and run the command "select * from
TripDetails;", you will see that results appear in under 2 seconds. If you
load the exact same database into the command line shell of version 3.19.3
and run the exact same select statement, it never produces results (or it
takes so long that I have never had the patience to hang around and see if
it does produce results).
I have verified that even under version 3.19.3, all the individual
views that contribute to the summary view produce results by themselves.
In fact pretty much everything except this summary view seems to work. So,
it looks like the massive inner join between these views is the cause of
the delay or failure in the latest version of sqlite. I am not sure how or
why, but I would appreciate it if others on this list who are more
knowledgeable about these things can take a look and let me know what they
think.
I am sure my db design leaves a lot to be desired in terms of
normalization, optimization, etc. I am open to suggestions on those
aspects, but my primary concern is that something that worked fine under a
previous version of sqlite does not work anymore. Whatever the flaws in
what I have done, I do expect things to not break simply when I upgrade to
the latest version of sqlite from a previous version. I would be open to
modifying my database in such a way that it is more efficient and faster,
and perhaps that enables me to produce the results I want from this query
in the latest version of sqlite. But to me that is secondary. I don't
want to be tweaking my database on an ongoing basis to make it perform well
with each new release of sqlite. The symptoms point to some kind of
regression in sqlite between 3.15 and 3.19.3, and I would like to see if
there is a fix that does not involve modifying my database.
Thank you very much.
Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
