On Monday, 24 February, 2014 21:53, mm.w <0xcafef...@gmail.com> said:
>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. Neither JOIN nor LEFT JOIN will work in this case because the RateClass and ClassRate are sparse relative to RawData. That is, there is RawData for every Month of every Year, but the RateClass only changes infrequently (a few times scattered over a few years) and the ClassRate also only changes occassionally (and not necessarily at the same time as the RateClass changed). One could project both sparse tables to cover the entire range of Year, Month and then do a simple equijoin, but it is far more complicated to project and join than to perform simple correlated subqueries to find the Class and Rate in effect at a given Year & Month. 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
