Truncate Dirty Tables Slowness

[cnizzardini]

I've been working on improving performance of a test suite and noticed seeing this for each test:

2023-03-09 12:56:31 Debug: connection=test duration=4402 rows=0 CALL TruncateDirtyTables();

Coming from MysqlTriggerBasedTableSniffer::truncateDirtyTables

Running these versions of libs on CakePHP 4.2 + Linux + PHP 7.4 + MySQL 8:

vierge-noire/cakephp-fixture-factories         v2.4                             CakePHP Dynamic Fixtures
vierge-noire/cakephp-test-migrator             v2.4                             Migration helper for the CakePHP Test Suite Light
vierge-noire/cakephp-test-suite-light          v2.3                             A test suite for CakePHP application based on Sql queries

Any recommendation on how to improve this performance? I verified its the main culprit by returning early in MysqlTriggerBasedTableSniffer::truncateDirtyTables but obviously is not a solution.

[pabloelcolombiano]

Hi @cnizzardini ,

the TruncateDirtyTables procediure is called if the test case uses the CakephpTestSuiteLight\Fixture\TruncateDirtyTables trait.

For all your tests that do not require the DB to be cleaned (namely those that do not interact with the DB), you do not need to use the trait.

This should improve the performance of your tests.

[pabloelcolombiano]

Note that if you are running CakePHP 4.2, then the approach is a bit different. See the docs here:
https://github.com/vierge-noire/cakephp-test-suite-light#prior-to-cakephp-43