Re: Optimize commit performance with a large number of 'on commit delete rows' temp tables

On Jul 8, 2024, at 12:18, wenhui qiu <qiuwenhuifx@gmail.com> wrote:

Hi feichanghong
    I don't think it's acceptable to introduce a patch to fix a problem that leads to performance degradation, or can we take tom's suggestion to optimise PreCommit_on_commit_actions?  I think it to miss the forest for the trees

You're right, any performance regression is certainly unacceptable. That's why

we've introduced a threshold. The bloom filter optimization is only applied

when the number of temporary tables exceeds this threshold. Test data also

reveals that with a threshold of 10, barring cases where all temporary tables

are implicated in a transaction, there's hardly any performance loss.

"Improve PreCommit_on_commit_actions by having it just quit immediately if

XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set" can only reduce the overhead of

traversing the OnCommitItem List but still doesn't address the issue with

temporary table truncation.

Looking forward to more suggestions!