Re: [sqlite] Joining 2 views
On Oct 14, 2008, at 4:39 AM, Guenther Schmidt wrote: > Hi, > > unfortunately I've hit a point where I run a query where one VIEW > joins > another VIEW. > > Both views are rather large and since there is no index on the fields > where they join the query takes very very long. (About 15 min). > > Does anybody here know a solution to this problem? What are the view definitions and what query are you running? It may be that you can add an index to one of the underlying real tables that will help. Dan. > Best regards > > Günther > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > 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
Re: [sqlite] Joining 2 views
Hi P, well I tried to put all the business logic out of my program and into views. Basically all my program does is feed data into tables that are created for dynamic data and are cleaned before each import. Once the import is finished my app then selects from those views. Those views use each other and for as long they do not join each other everything is fine. I did not want to use Temp tables because AFAIK I need to create and drop them programmitcally and that means that I have to put part of the business logic back into my app, which I had hoped to avoid. Before using sqlite I had used Access for that, which seems so index even views, but at some point access threw unexpected errors when I then tried to access those views of views via odbc, why I don't now but that's when I switched to sqlite and this problem came up. Best regards Guenther P Kishor schrieb: > On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: > >> Hi P, >> >> thanks, I had considered that. >> >> Hope there is another solution though. >> > > possibly, but how about enlightening others as to why you considered > creating temp tables and then decided not to do so? > > In fact, how about explaining in an email all the things that you have > considered, tried and rejected so others may learn from it? > > > >> Günther >> >> P Kishor schrieb: >> On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: >> >> >> Hi, >> >> unfortunately I've hit a point where I run a query where one VIEW joins >> another VIEW. >> >> Both views are rather large and since there is no index on the fields >> where they join the query takes very very long. (About 15 min). >> >> Does anybody here know a solution to this problem? >> >> create temporary tables with the views, possibly index these temp >> tables, then query that table. >> >> >> >> Best regards >> >> Günther >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> 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
Re: [sqlite] Joining 2 views
On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: > > Hi P, > > thanks, I had considered that. > > Hope there is another solution though. possibly, but how about enlightening others as to why you considered creating temp tables and then decided not to do so? In fact, how about explaining in an email all the things that you have considered, tried and rejected so others may learn from it? > > Günther > > P Kishor schrieb: > On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: > > > Hi, > > unfortunately I've hit a point where I run a query where one VIEW joins > another VIEW. > > Both views are rather large and since there is no index on the fields > where they join the query takes very very long. (About 15 min). > > Does anybody here know a solution to this problem? > > create temporary tables with the views, possibly index these temp > tables, then query that table. > > > > Best regards > > Günther > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Joining 2 views
Hi P, thanks, I had considered that. Hope there is another solution though. Günther P Kishor schrieb: > On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: > >> Hi, >> >> unfortunately I've hit a point where I run a query where one VIEW joins >> another VIEW. >> >> Both views are rather large and since there is no index on the fields >> where they join the query takes very very long. (About 15 min). >> >> Does anybody here know a solution to this problem? >> > > create temporary tables with the views, possibly index these temp > tables, then query that table. > > >> Best regards >> >> Günther >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> 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
Re: [sqlite] Joining 2 views
On 10/13/08, Guenther Schmidt <[EMAIL PROTECTED]> wrote: > Hi, > > unfortunately I've hit a point where I run a query where one VIEW joins > another VIEW. > > Both views are rather large and since there is no index on the fields > where they join the query takes very very long. (About 15 min). > > Does anybody here know a solution to this problem? create temporary tables with the views, possibly index these temp tables, then query that table. > > Best regards > > Günther > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Joining 2 views
Hi, unfortunately I've hit a point where I run a query where one VIEW joins another VIEW. Both views are rather large and since there is no index on the fields where they join the query takes very very long. (About 15 min). Does anybody here know a solution to this problem? Best regards Günther ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
