Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Frank Millman
On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote: > SELECT > acc_no, > acc_name, > SUM(i_90.invoice_bal) AS 90_days, > SUM(i_current.invoice_bal) AS current > FROM >debtors_table > LEFT JOIN > invoices i_90 > ON >

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread nomad
On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote: > SELECT > acc_no, > acc_name, > SUM(i_90.invoice_bal) AS 90_days, > SUM(i_current.invoice_bal) AS current > FROM >debtors_table > LEFT JOIN > invoices i_90 > ON >

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread nomad
On Mon Sep 11, 2017 at 08:40:41AM +0200, Frank Millman wrote: > I could structure it like this (pseudo code) - > > 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

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Frank Millman
Clemens Ladisch wrote: > Frank Millman wrote: > > 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 [...] To > force the ageing results to be stored, you'd have to create a

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Clemens Ladisch
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