Table-valued parameter performing poorly in EF Core vs T-SQL directly

Question

I have a custom table type (object/column names obfuscated)

CREATE TYPE [dbo].[myTable] AS TABLE
(
    my_id    BIGINT, 
    my_name  VARCHAR(100),
    my_value VARCHAR(100)
)
GO

And a procedure that uses it as an input parameter:

CREATE OR ALTER procedure my_merge_sp
    (@downloaded_records [myTable] READONLY,
     @last_updated_on    DATETIME,      
     @last_updated_by    VARCHAR(128))
AS
BEGIN
    -- DO STUFF
END

When using SSMS, I declare a new instance of myTable and insert 200K records into it, and call my stored procedure. It takes 2 seconds.

When using EF Core, I declare a new SqlParameter using a DataTable that matches the shape of the type, and put the same 200K records in it. The command that executes the stored procedure times out (with a command timeout set to 120 seconds).

FWIW, I tested this call with 50 records from EF Core and it took 1 second, and the stored procedure did what it was supposed to, so the EF Core is wired up correctly from that standpoint.

But otherwise, what is the key difference between what EF Core is doing and what is going on in SSMS to account for that massive performance difference? And what tricks can I leverage to bring the EF Core performance to be more on par with SSMS?

Answer 1

TLDR - WITH RECOMPILE fixed the issue. (explanation below)

Based on the comments section, I tried a few things:

One, I actually gutted the stored procedure so that it was doing nothing but a print statement. I wanted to see if the slowness was caused by the size of the table valued input parameter, or if it was the actual contents of the stored procedure. Without the stored procedure doing anything of substance, it returned very quickly. This ruled out the theory that the data going over the wire was to blame.

Next, I realized that in all of my SSMS tests, I had only tested with large datasets. Through EF Core, I had tested with datasets of all sizes, starting with small ones. Based on what a lot of commenters suggested, SSMS and EF Core may cache plans in SQL server separately from one another. Given that SSMS had only been tested with large datasets, it's only cached plan was one optimized for large datasets.

But given that in EF Core, I had first tested with a small dataset, that was what the cached plan was optimized for, and that's why it was so slow with the large dataset. I updated the stored procedure using the WITH RECOMPILE keyword. This didn't add any significant overhead to the execution. But it did make the calls from EF Core snappy, even alternating between small and large datasets.

Thank you to everyone who commented. The sum of all the comments steered me in the right direction.