Re: Slow while inserting and retrieval (compared to SQL Server)

> De: "sivapostgres" <sivapostgres@yahoo.com>
> Para: "Benedict Holland" <benedict.m.holland@gmail.com>
> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>, "Thomas Kellerer" <shammat@gmx.net>
> Enviadas: Quarta-feira, 17 de fevereiro de 2021 11:09:38
> Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

> So far no performance tuning done for sql server.  It works fine for the load.
> Even the express edition which is free, works better.  I don't think postgres
> will be so slow to insert such a low number of records in an empty database.
> I'll be preparing the required sql statements to insert those records in pgadmin
> and see the timings, tomorrow.
>
>    On Wednesday, 17 February, 2021, 07:29:29 pm IST, Benedict Holland
>    <benedict.m.holland@gmail.com> wrote:
>
> Sql server is a 10k dollar to 1 million dollar application (or more) that is
> specifically optimized for windows and had limited to no support anywhere else.
> Postgres is free and from my experience, comes within 5% of any other dbms.
> Inserting 1 row at a time with auto commit on will be a bit slow but it
> shouldn't be noticeable. What times are you seeing if you do this with pgadmin4
> compared to sql server? Also, have you done any performance tuning for postgres
> server? There are many documents detailing performance tuning your servers,
> like you probably did, at some point, with your sql server.
> Thanks,Ben
> On Wed, Feb 17, 2021, 8:28 AM sivapostgres@yahoo.com <sivapostgres@yahoo.com>
> wrote:
>
> We use datawindows.  Datawindows will send the required DML statements to the
> database.  And it sent in format 1 <single row update>.
> IN start of the application, Autocommit set to True.Before update of any
> table(s)Autocommit is set to FalseInsert/Update/Delete recordsIf success commit
> else rollbackAutocommit is set to True
> This has been followed for decades and it's working fine with Sql server.
> Here we are trying to insert just 10 records spread across 6 tables, which is
> taking more time.. that's what we feel.   The similar work in SQL Server takes
> much less time < as if no wait is there >.
>    On Wednesday, 17 February, 2021, 06:48:35 pm IST, Thomas Kellerer
>    <shammat@gmx.net> wrote:
>
> sivapostgres@yahoo.com schrieb am 17.02.2021 um 13:01:
>> To populate some basic data we try to insert few records (max 4
>> records) in few tables (around 6 tables) from one window.  We feel
>> that the insert time taken is longer than the time taken while using
>> Sql Server.  We tested almost a similar window that updated the
>> similar table(s) in SQL server, which was faster.  With Postgres
>> database, we need to wait for a couple of seconds before the
>> insert/update is over, which we didn't feel in Sql Server.
>
>
> Are you doing single-row inserts like:
>
>    insert into ... values (..);
>    insert into ... values (..);
>    insert into ... values (..);
>    insert into ... values (..);
>
> or are you doing multi-row inserts like this:
>
>    insert into ... values (..), (..), (..), (..);
>
> Typically the latter will perform much better (especially if autocommit is
> enabled)

Please provide the EXPLAIN ANALYZE plans so we can take a look at what is causing these 'slow' inserts.