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

[Benedict Holland <benedict.m.holland@xxxxxxxxx>]

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@xxxxxxxxx <sivapostgres@xxxxxxxxx> 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 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@xxxxxxx> wrote:

sivapostgres@xxxxxxxxx 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)