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

----- Mensagem original -----

De: sivapostgres@yahoo.com
Para: "pgsql-general" <pgsql-general@lists.postgresql.org>
Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:01:15
Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

Hello,

Using Postgres 11 in Windows Server 2012 & Powerbuilder
Working from the same machine where Postgres 11 is installed. So no chance for any network issue, I feel.
No setup/config change done. Just working with all the default settings.
With no data in any of the 179 tables in that database.

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.

I feel that some changes in settings might improve this speed, but with not much knowledge in Postgres I struggle to find out those setup values.

Any help in improving the speed is really appreciated.

Happiness Always
BKR Sivaprakash

Can you run EXPLAIN (ANALYZE,BUFFERS) on the INSERT command and post the results here? Usually inserting this many records should be instantaneous.

Hello,Yes, that's what I feel.  With no records in any tables, working from the same machine where PG has been installed, with only one user working, inserting few records (10 records in total, in all 6 tables)  should not take this much time.  
I'll be inserting records from PowerBuilder applications, and how to catch the result of Explain.  OR should I run all insert commands in PG Admin or so and catch those results?  
As I'm new to PG, any documentation/help in this direction will be useful.
Happiness AlwaysBKR Sivaprakash

On Wednesday, 17 February, 2021, 05:35:43 pm IST, <luis.roberto@siscobra.com.br> wrote:

----- Mensagem original -----

De: sivapostgres@yahoo.com
Para: "pgsql-general" <pgsql-general@lists.postgresql.org>
Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:01:15
Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

Hello,

Using Postgres 11 in Windows Server 2012 & Powerbuilder
Working from the same machine where Postgres 11 is installed. So no chance for any network issue, I feel.
No setup/config change done. Just working with all the default settings.
With no data in any of the 179 tables in that database.

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.

I feel that some changes in settings might improve this speed, but with not much knowledge in Postgres I struggle to find out those setup values.

Any help in improving the speed is really appreciated.

Happiness Always
BKR Sivaprakash

Can you run EXPLAIN (ANALYZE,BUFFERS) on the INSERT command and post the results here? Usually inserting this many records should be instantaneous.

----- Mensagem original -----

De: "sivapostgres" <sivapostgres@yahoo.com>
Para: "luis.roberto" <luis.roberto@siscobra.com.br>
Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>
Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:54:18
Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

Hello,
Yes, that's what I feel. With no records in any tables, working from the same
machine where PG has been installed, with only one user working, inserting few
records (10 records in total, in all 6 tables) should not take this much time.

I'll be inserting records from PowerBuilder applications, and how to catch the
result of Explain. OR should I run all insert commands in PG Admin or so and
catch those results?

As I'm new to PG, any documentation/help in this direction will be useful.

Happiness Always
BKR Sivaprakash

On Wednesday, 17 February, 2021, 05:35:43 pm IST, <luis.roberto@siscobra.com.br>
wrote:

You can run it wherever you prefer. I'm not familiar with PowerBuilder, so I'd say PGAdmin.
BTW, if you run the INSERTs on PGAdmin, do you still seeing slow timings?

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)

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)

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:

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)

Yea. Let's see what the metrics actually are. It is possible that the data
gets loaded in instantly but for whatever reason you do t see the reflected
changes.

On Wed, Feb 17, 2021, 9:09 AM sivapostgres@yahoo.com <sivapostgres@yahoo.com>
wrote:

sivapostgres@yahoo.com schrieb am 17.02.2021 um 14:27:

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 >.

The rollback can't work if autocommit is set to true.
Did you mean "IN start of the application, Autocommit set to FALSE"?

However these simple statements shouldn't take substantially longer
on Postgres than on SQL Server.

If you are DELETEing from tables with foreign keys: are all FK columns indexed?
That can make a huge difference if you delete from the parent table,
but the FK columns on the children aren't indexed.

It would be really interesting to see the execution plans generated
"explain (analyze)" for the DELETE and UPDATE statements.

ATTENTION: explain (analyze) actually runs that statement so it will do the
deletes or updates. Make sure you run that in a transaction so you can rollback.

Thomas

On 2021-02-17 12:54:18 +0000, sivapostgres@yahoo.com wrote:

Yes, that's what I feel. With no records in any tables, working from the same
machine where PG has been installed, with only one user working, inserting few
records (10 records in total, in all 6 tables) should not take this much
time.

How much time is "this much time"? Are we talking a few milliseconds
here? Less? More? Much more?

It's hard to give advice if you don't tell us more than "slower than SQL
server". Please be specific. Use actual numbers.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

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.