Hello, I don't want to be annoying but why nowadays people are sub-abusing-sub-selecting instead of using JOINs? moreover, that is in most cases faster (a lot) and certainly more Human Readable.
Best Regards On Mon, Feb 24, 2014 at 8:34 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > Previous send munged. If it managed to make it properly, my apologies for > posting the same message twice. > > Using the following schema: > > CREATE TABLE TKRawData > ( > EmpNo text collate nocase not null, > CustNo integer not null, > JobNo integer not null, > RawYear integer not null, > RawMonth integer not null, > RawDays real not null, > primary key (EmpNo, JobNo, CustNo, RawYear, RawMonth) > ); > > CREATE TABLE TKClassRates > ( > RateClass integer not null, > Year integer not null, > Month integer not null, > Rate real not null, > primary key (RateClass, Year, Month) > ); > > CREATE TABLE TKEmpRateClass > ( > EmpNo text not null, > Year integer not null, > Month integer not null, > RateClass integer not null, > primary key (EmpNo, Year, Month) > ); > > CREATE INDEX TKClassRatesCover on TKClassRates (RateClass, Year desc, > Month desc, Rate); > > CREATE INDEX TKEmpRateClassCover on TKEmpRateClass (EmpNo, Year desc, > Month desc, RateClass); > > CREATE VIEW TKRawDataRate > as > select *, > RawDays*RawRate as RawCharge > from ( select *, > coalesce(( select Rate > from TKClassRates > where RateClass = RawRateClass > and Year*12+Month-1 <= > RawYear*12+RawMonth-1 > order by Year desc, Month desc limit 1), 1) as > RawRate > from ( select *, > coalesce(( select RateClass > from TKEmpRateClass > where EmpNo = TKRawData.EmpNo > and Year*12+Month-1 <= > RawYear*12+RawMonth-1 > order by Year desc, Month desc limit > 1), 1) as RawRateClass > from TKRawData > ) as T1 > ) as T2; > > Doing the following: > > SELECT * FROM TKRawDataRate; > > results in the following plan: > > sqlite> explain query plan select * from tkrawdatarate; > 0|0|0|SCAN TABLE TKRawData > 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 > 1|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover > (EmpNo=?) > 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 > 2|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover > (RateClass=?) > 2|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 > 3|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover > (EmpNo=?) > 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4 > 4|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover > (RateClass=?) > 4|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5 > 5|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover > (EmpNo=?) > sqlite> > > Results are correct and the plan is the same with or without analyze > (there is really only one way to answer the query). The query planner > seems to be substituting the correlated subqueries each time the > intermediate is referenced rather than executing the subquery once and > passing the result up/along. > > create view TKRawDataTest > as > select *, > RawDays * coalesce(( select Rate > from TKClassRates > where RateClass = coalesce(( select > RateClass > from > TKEmpRateClass > where EmpNo = > TKRawData.EmpNo > and > Year*12+Month-1 <= RawYear*12+RawMonth-1 > order by Year > desc, Month desc limit 1), 1) > and Year*12+Month-1 <= > RawYear*12+RawMonth-1 > order by Year desc, Month desc limit 1), 1) as > RawCharge > from TKRawData; > > however, does generate a plan with only one execution of each correlated > subquery, but does not give me access to the intermediate results (it also > generates correct results) > > SELECT * FROM TKRawDataTest; > > sqlite> explain query plan select * from tkrawdatatest; > 0|0|0|SCAN TABLE TKRawData > 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 > 1|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover > (RateClass=?) > 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 > 2|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover > (EmpNo=?) > > > > > _______________________________________________ > 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
