dotnet
ToQueryPlan to... get the query plan...
Very cool idea suggested by @lauxjpn: we now have ToQueryString, we could also get the query plan from the database.
SQL Server:
SET SHOWPLAN_ALL ON;
<query>
SET SHOWPLAN_ALL OFF;
PostgreSQL / SQLite
EXPLAIN <query>
When this has been implemented, this basically screams for an extension, that allows users to specify execution duration limits, that will log the query together with the query plan, once those limits get hit.
We may as well just find out what's wrong with the queries and fix it too :rofl:
Seriously, this is a really nice direction.
This has challenges that ToQueryString doesn't have--most notably that it requires a connection to the database to run the "explain" query. So this probably needs a bit more thought, but integrating this in some way with EF Core would be very useful.
I just wrote an extension method along these lines
static class SqliteExtensions
{
public static String ToQueryPlan(this IQueryable queryable)
{
using var command = queryable.CreateDbCommand();
command.CommandText = "explain query plan " + command.CommandText;
using var reader = command.ExecuteReader();
reader.Read();
return reader.GetString(3);
}
public static async Task<String> ToQueryPlanAsync(this IQueryable queryable, CancellationToken ct = default)
{
await using var command = queryable.CreateDbCommand();
command.CommandText = "explain query plan " + command.CommandText;
await using var reader = await command.ExecuteReaderAsync(ct);
await reader.ReadAsync(ct);
return reader.GetString(3);
}
}
It's not a method but I built a VS debugger visualizer to view query plans inside Visual Studio: EFCoreVisualizer
Requires VS 17.9 which was released yesterday.
@Giorgi this is super nicer! Obviously the visualization is also much better than any text-based dump we could integrate.
Did you write the visualization yourself, or could you leverage any existing SQL Server component to do that for you?
@ErikEJ mysql-workbench has some code for displaying a graphical query plan.
Seems to be done by the wb.query.analysis python plugin.
10.8.2 EXPLAIN Output Format describes the format that EXPLAIN outputs.