On 2021-03-19 11:04 AM, Thomas Kellerer wrote:
Frank Millman schrieb am 19.03.2021 um 09:52:
I am writing a cross-platform accounting app, and I test using Sql
Server on Windows 10 and PostgreSql on Fedora 31. Performance is
usually very similar, with a slight edge to PostgreSql. Now I have a
SELECT which runs over twice as fast on Sql Server compared to
PostgreSql.
Can you change the SELECT statement?
Very often "distinct on ()" is faster in Postgres compared to the equivalent solution using window functions
Thanks, Thomas
I tried that, and it ran about 10% faster. Every little helps, but SQL Server appears to have some secret sauce!
The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing - at least I can't spot a difference.
If that is correct, you can move them into a common table expression - maybe detecting that is SQL Server's secret sauce.
with totals as (
SELECT a.source_code_id, SUM(a.tran_tot) AS total
FROM (
SELECT distinct on (location_row_id, function_row_id, source_code_id) source_code_id, tran_tot
FROM prop.ar_totals
WHERE deleted_id = 0
AND tran_date <= '2018-03-31'
AND ledger_row_id = 1
ORDER BY location_row_id, function_row_id, source_code_id, tran_date DESC
) AS a
GROUP BY a.source_code_id
)
select
'2018-03-01' AS op_date, '2018-03-31' AS cl_date,
cl_bal.source_code_id, op_bal.total as op_tot, cl_bal.total.cl_tot
FROM totals as cl_bal
LEFT JOIN totals as op_bal ON op_bal.source_code_id = cl_bal.source_code_id;
There is a difference.
cl_bal selects WHERE tran_date <= '2018-03-31'.
op_bal selects WHERE tran_date < '2018-03-01'.
The second one could be written as WHERE tran_date <= '2018-02-28', but
I don't think that would make any difference.
Frank
