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

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 False

Insert/Update/Delete records

If success commit else rollback

Autocommit 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)