> De: "sivapostgres" <sivapostgres@xxxxxxxxx> > Para: "Benedict Holland" <benedict.m.holland@xxxxxxxxx> > Cc: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>, "Thomas Kellerer" <shammat@xxxxxxx> > 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@xxxxxxxxx> 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@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 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@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) Please provide the EXPLAIN ANALYZE plans so we can take a look at what is causing these 'slow' inserts.
