Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 9:53 odesílatel Frank Millman <frank(at)chagford(dot)com> napsal:

>
> On 2021-03-19 10:29 AM, Thomas Kellerer wrote:
> > Frank Millman schrieb am 19.03.2021 um 09:19:
> >> This may be a non-issue, and I don't want to waste your time. But
> perhaps someone can have a look to see if there is anything obvious I have
> missed.
> >>
> >> 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
> >
> > Something along the lines (for the first derived table):
> >
> > SELECT ...
> > FROM (
> > SELECT a.source_code_id, SUM(a.tran_tot) AS cl_tot
> > 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
> > ) as cl_bal
> > ...
> Thanks, Thomas
>
> I tried that, and it ran about 10% faster. Every little helps, but SQL
> Server appears to have some secret sauce!
>

can you send a result of EXPLAIN ANALYZE?

Pavel

> Frank
>
>
>
>
>