Massive perf regression when reverse engineering using Microsoft.Data.SqlClient in connection string provider #76
Description
I realize there isn't currently intent to fully retool for using the new client, but it does actually work when used in the connection string. The kicker is, the SQL that's generated to reverse engineer the schema does not get the AddOptionMergeJoinInterceptor for anything but the System.Data.SqlClient SqlServerInvariantName (see IsSQLServer check in the constructor of EntityStoreSchemaGeneratorDatabaseSchemaLoader ) .
The interceptor was added way back in EF 6.2.0 in 2018 and works great for the newer cardinality estimators, but is missing (for obvious reasons in the code) in the profiled query that gets executed when EF tries to query the metadata to reverse engineer the schema.
It would seem a simple change to look for either of the clients to determine if the Interceptor should be used would reintroduce the perf gain for the generated SQL while still maintaining the capability of using the flag to disable it for those in need (who probably would already have it set).
I'm kind of new to digging into the bowls of the EF tooling, so I'd love to hear if others in the know agree or disagree with the potential change?
And perhaps I should have started with this, but the reverse engineering process takes about 1 minute against my DB when the OPTION(MERGE JOIN) is present and about 28min when it isn't while producing the same results.
Thoughts?