Re: SELECT is faster on SQL Server

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