Frank Millman wrote: > SELECT acc_no, acc_name, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date <= date_5) > AS 120_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_5 > AND invoice_date <= date_4) AS 90_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_4 > AND invoice_date <= date_3) AS 60_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_3 > AND invoice_date <= date_2) AS 30_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_2 > AND invoice_date <= date_1) AS current > FROM debtors_table
This query does not make sense without a GROUP BY and a correlation, which would affect how to optimize it. > This works, but it requires 5 separate scans of the invoice table, > which is inefficient. If there is an index on the invoice_date, it's efficient. Assuming that there is an account_numer filter you did not show, the index should look like this: CREATE INDEX invoices_accno_date_idx ON invoices(account_number, invoice_date); Alternatively, to create a covering index (http://www.sqlite.org/queryplanner.html#covidx) for this query, add the invoice_bal column. > I changed it to use a WITH clause to make one scan of the invoice > table and store the results. The WITH clause itself does not store anything; it's syntactic sugar. > WITH ageing AS (...) > SELECT acc_no, acc_name, > (SELECT balance FROM ageing WHERE account_number = acc_no AND > bucket_number = 5) AS 120_days, > (SELECT balance FROM ageing WHERE account_number = acc_no AND > bucket_number = 4) AS 90_days, > (SELECT balance FROM ageing WHERE account_number = acc_no AND > bucket_number = 3) AS 60_days, > (SELECT balance FROM ageing WHERE account_number = acc_no AND > bucket_number = 2) AS 30_days, > (SELECT balance FROM ageing WHERE account_number = acc_no AND > bucket_number = 1) AS current > FROM debtors_table > > It works, but it takes about 3.5 seconds to execute. The ageing query is likely to be executed five times for each row in the outer query. (This might be efficient with the above the index.) To force the ageing results to be stored, you'd have to create a temporary table. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
