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