Hi all
I am writing an accounting system, and want to select ‘aged balances’ from the
debtors table.
To do this, I need to assign each outstanding invoice to an ageing ‘bucket’,
where each bucket contains the sum of invoices where date > start_date and date
<= end_date. There will be five buckets altogether. In my test database I have
12 debtors and a few thousand invoices.
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 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 works, but it requires 5 separate scans of the invoice table, which is
inefficient.
I changed it to use a WITH clause to make one scan of the invoice table and
store the results.
I won’t show the WITH clause here, as it is not the reason for the question,
and would just add clutter. If I run the WITH clause separately, it executes in
about 0.15 seconds, and generates 60 rows with 3 columns – account number,
bucket number, and accumulated balance.
In the main clause I now have this -
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.
Exactly the same exercise on Sql Server takes 0.17 seconds, and on PostgreSQL
0.22 seconds.
I appreciate that the temporary table created in the WITH clause is not
indexed, but 3.5 seconds seems a long time to extract the data from 60 rows.
Is there any way to speed this up?
Frank Millman
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
