Scaffolding huge database (3235 tables) takes 12 min compared to MySql.EntityFrameworkCore with 1 min

[dietrichha]

Hello,

the performance of scaffolding in Pomelo.EntityFrameworkCore.MySql is very slow compared to MySql.EntityFrameworkCore. Both provider succeed in scaffolding a huge database with 3235 tables.
Only the time to complete the operation differs extremly. Pomelo.EntityFrameworkCore.MySql takes 12 min and MySql.EntityFrameworkCore takes 1 min for the scaffolding operation. I would not complain at all, but i wanted to report this behaviour, as the difference is huge, and could be a simple bug.

To Reproduce
Pomelo.EntityFrameworkCore.MySql:

dotnet ef dbcontext scaffold "name=Connection" Pomelo.EntityFrameworkCore.MySql --no-onconfiguring
--context Context --context-dir ..\Project\Data
--output-dir ..\Project\Model --force
--namespace "Project.Model" `
--context-namespace "Project.Data"

MySql.EntityFrameworkCore:

dotnet ef dbcontext scaffold "name=Connection" MySql.EntityFrameworkCore --no-onconfiguring
--context Context --context-dir ..\Project\Data
--output-dir ..\Project\Model --force
--namespace "Project.Model" `
--context-namespace "Project.Data"

Expected behavior
I would expect Pomelo.EntityFrameworkCore.MySql to take double the time of another db provider, but not 12x.

Technical details (please complete the following information):

• Database server version: MySQL 8.0.39
• Operating system: Windows 11
• Pomelo.EntityFrameworkCore.MySql version: 9.0.0-preview.3.efcore.9.0.0

[lauxjpn]

@dietrichha Thanks for letting us know. I think we have never checked the performance of the scaffolding process.

I now did some local tests on my machine, for which I generated a database with 1,000 tables, each table having 21 columns (1 PK, 5 FKs and 15 other columns). A sample table would look like this:

Sample table SQL script

CREATE TABLE table0 (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `column0` INT NULL,
    `column1` VARCHAR(255) NULL,
    `column2` DATETIME(6) NULL,
    `column3` INT NULL,
    `column4` VARCHAR(255) NULL,
    `column5` DATETIME(6) NULL,
    `column6` INT NULL,
    `column7` VARCHAR(255) NULL,
    `column8` DATETIME(6) NULL,
    `column9` INT NULL,
    `column10` VARCHAR(255) NULL,
    `column11` DATETIME(6) NULL,
    `column12` INT NULL,
    `column13` VARCHAR(255) NULL,
    `column14` DATETIME(6) NULL,
    `table668_id_0` INT NULL,
    `table140_id_1` INT NULL,
    `table125_id_2` INT NULL,
    `table522_id_3` INT NULL,
    `table168_id_4` INT NULL
);

ALTER TABLE `table0` ADD CONSTRAINT `fk_table0_table668_id_0` FOREIGN KEY (`table668_id_0`) REFERENCES `table668` (`id`);
ALTER TABLE `table0` ADD CONSTRAINT `fk_table0_table140_id_1` FOREIGN KEY (`table140_id_1`) REFERENCES `table140` (`id`);
ALTER TABLE `table0` ADD CONSTRAINT `fk_table0_table125_id_2` FOREIGN KEY (`table125_id_2`) REFERENCES `table125` (`id`);
ALTER TABLE `table0` ADD CONSTRAINT `fk_table0_table522_id_3` FOREIGN KEY (`table522_id_3`) REFERENCES `table522` (`id`);
ALTER TABLE `table0` ADD CONSTRAINT `fk_table0_table168_id_4` FOREIGN KEY (`table168_id_4`) REFERENCES `table168` (`id`);

With that setup, I then ran the following PowerShell script for Pomelo:

PowerShell script for Pomelo.EntityFrameworkCore.MySql scaffolding performance test

& {
    Remove-Item 'Issue2005_Pomelo' -Recurse -Force -ErrorAction SilentlyContinue
    dotnet new console --name 'Issue2005_Pomelo'

    Push-Location
    try {
        cd 'Issue2005_Pomelo'
        dotnet add package 'Microsoft.EntityFrameworkCore.Design' --version '9.0.7'
        dotnet add package 'Pomelo.EntityFrameworkCore.MySql' --version '9.0.0-rc.1.efcore.9.0.0'
        dotnet tool install dotnet-ef --version '9.0.7' --create-manifest-if-needed

        dotnet build

        $startTime = Get-Date

        dotnet ef dbcontext scaffold `
        'server=127.0.0.1;port=3306;user=root;password=;database=large_schema' 'Pomelo.EntityFrameworkCore.MySql' `
        --output-dir 'Models' `
        --namespace 'Project.Model' `
        --context-dir 'Data' `
        --context-namespace 'Project.Data' `
        --context 'LargeSchemaContext' `
        --no-onconfiguring `
        --force `
        --verbose `
        --no-build

        "Time spent scaffolding with Pomelo.EntityFrameworkCore.MySql: $(((Get-Date) - $startTime).TotalSeconds) seconds"
    }
    finally {
        Pop-Location
    }
}

This resulted in:

Time spent scaffolding with Pomelo.EntityFrameworkCore.MySql: 32.8457318 seconds

I then ran the following PowerShell script for Oracle's implementation:

PowerShell script for MySql.EntityFrameworkCore scaffolding performance test

& {
    Remove-Item 'Issue2005_Oracle' -Recurse -Force -ErrorAction SilentlyContinue
    dotnet new console --name 'Issue2005_Oracle'
    
    Push-Location
    try {
        cd 'Issue2005_Oracle'
        dotnet add package 'Microsoft.EntityFrameworkCore.Design' --version '9.0.7'
        dotnet add package 'MySql.EntityFrameworkCore' --version '9.0.6'
        dotnet tool install dotnet-ef --version '9.0.7' --create-manifest-if-needed
    
        dotnet build
    
        $startTime = Get-Date
    
        dotnet ef dbcontext scaffold `
        'server=127.0.0.1;port=3306;user=root;password=;database=large_schema' 'MySql.EntityFrameworkCore' `
        --output-dir 'Models' `
        --namespace 'Project.Model' `
        --context-dir 'Data' `
        --context-namespace 'Project.Data' `
        --context 'LargeSchemaContext' `
        --no-onconfiguring `
        --force `
        --verbose `
        --no-build
    
        "Time spent scaffolding with MySql.EntityFrameworkCore: $(((Get-Date) - $startTime).TotalSeconds) seconds"
    }
    finally {
        Pop-Location
    }
}

This resulted in:

Time spent scaffolding with MySql.EntityFrameworkCore: 21.9715884 seconds

So while Oracle's MySQL provider seems to be scaffolding faster then Pomelo at the moment, Pomelo takes 1.5 times as long as the Oracle MySQL provider in my tests.

I attached my database schema script I used for testing, in case you want to repeat the results on your machine.

If this script scaffolds comparably to my test on your machine, but your real-world schema scaffolds 11 times slower, then we would need to dive further into how your real-world database differs from my test script.

You could also check, whether you get the same bad performance, if you scaffold from a schema-only database (a local copy of your production database that has no data).

mysql_scaffold_test__large_schema.zip

[lauxjpn]

I can confirm, that with 3,000 tables (instead of 1,000 tables), the results I get are quite a bit worse:

Time spent scaffolding with Pomelo.EntityFrameworkCore.MySql: 548.6875155 seconds

So the scaffolding time seems to increase exponentially.

As for Oracle's MySQL provider implementation, I got a command timeout exception when scaffolding 3k tables, probably due to some cartesian explosion when executing a query. I had to add a DefaultCommandTimeout=600 option to the connection string, so that the scaffolding would even complete.

Time spent scaffolding with MySql.EntityFrameworkCore: 521.0706505 seconds

So in my test with 3k tables, the Oracle MySQL provider is only slightly faster.

---

Looking at our code, we are executing 4-6 queries per table, so between 12k and 18k queries for the scaffolding process of 3k tables. We should be able to reduce this to 0-1 table specific queries (1 in case the table uses full text indices) and around 10 queries for the whole scaffolding process overall.