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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

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

sivapostgres@yahoo.com
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

Reply | Threaded
Open this post in threaded view
|

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

luis.roberto
----- Mensagem original -----

> De: [hidden email]
> Para: "pgsql-general" <[hidden email]>
> 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.


Reply | Threaded
Open this post in threaded view
|

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

sivapostgres@yahoo.com
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, <[hidden email]> wrote:


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

> De: [hidden email]
> Para: "pgsql-general" <[hidden email]>
> 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.



Reply | Threaded
Open this post in threaded view
|

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

luis.roberto

----- Mensagem original -----
> De: "sivapostgres" <[hidden email]>
> Para: "luis.roberto" <[hidden email]>
> Cc: "pgsql-general" <[hidden email]>
> 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, <[hidden email]>
> 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?


Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
In reply to this post by sivapostgres@yahoo.com
[hidden email] 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)



Reply | Threaded
Open this post in threaded view
|

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

sivapostgres@yahoo.com
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 <[hidden email]> wrote:


[hidden email] 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)




Reply | Threaded
Open this post in threaded view
|

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

Benedict Holland
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 [hidden email] <[hidden email]> 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 <[hidden email]> wrote:


[hidden email] 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)




Reply | Threaded
Open this post in threaded view
|

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

sivapostgres@yahoo.com
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 <[hidden email]> 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 [hidden email] <[hidden email]> 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 <[hidden email]> wrote:


[hidden email] 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)




Reply | Threaded
Open this post in threaded view
|

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

Benedict Holland
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 [hidden email] <[hidden email]> 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 <[hidden email]> 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 [hidden email] <[hidden email]> 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 <[hidden email]> wrote:


[hidden email] 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)




Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
In reply to this post by sivapostgres@yahoo.com
[hidden email] 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


Reply | Threaded
Open this post in threaded view
|

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

Peter J. Holzer
In reply to this post by sivapostgres@yahoo.com
On 2021-02-17 12:54:18 +0000, [hidden email] 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.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

luis.roberto
In reply to this post by sivapostgres@yahoo.com
> De: "sivapostgres" <[hidden email]>
> Para: "Benedict Holland" <[hidden email]>
> Cc: "pgsql-general" <[hidden email]>, "Thomas Kellerer" <[hidden email]>
> 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
>    <[hidden email]> 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 [hidden email] <[hidden email]>
> 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
>    <[hidden email]> wrote:
>
> [hidden email] 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.