explain query plan select * from TripDetails Causes problems as well.
RBS On Sun, Jun 11, 2017 at 5:27 AM, Balaji Ramanathan < balaji.ramanat...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
