sql server - Table-valued parameter performing poorly in EF Core vs T-SQL directly - Stack Overflow

admin管理员组

文章数量:1122832

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?

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?

• sql-server
• performance
• entity-framework-core

Share Improve this question Follow edited Nov 25, 2024 at 20:25 ipodtouch0218 3,16899 gold badges1414 silver badges2929 bronze badges asked Nov 22, 2024 at 22:42 MikeMike 15811 silver badge99 bronze badges 12

• Avoid EF Core's LINQ-based abstractions for stored procedures. Use raw SQL for passing the TVP. dbContext.Database.ExecuteSqlRawAsync("EXEC MyStoredProcedure @MyTable", parameter); – Tharuka Deshan Commented Nov 23, 2024 at 3:08
• @TharukaDeshan - thx for the suggestion. So in my case, with 200K rows, would I just be creating a massive string with 200k individual inserts? Like a stringbuilder of epic proportions? – Mike Commented Nov 23, 2024 at 3:19
• 2 Dont think the problem is linq, unless it takes a lot of time to populate the tvp itself. Probably your procedure has some bad query plans cached. Try adding a pk to the tvp perhaps and check the running query and it's queryplan – siggemannen Commented Nov 23, 2024 at 7:36
• Among other things SSMS defaults to SET ARITHABORT ON, unlike most other connection methods, so EF probably has a different and unfavorable query plan cached already. – AlwaysLearning Commented Nov 23, 2024 at 10:04
• I feel huge difference in your tests. Table populated on the SQL Server side and there is no network between you SP and Table. In case of TVP, you should send megabaytes over network before SP call. – Svyatoslav Danyliv Commented Nov 23, 2024 at 11:59

 |  Show 7 more comments

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： sql serverTablevalued parameter performing poorly in EF Core vs TSQL directlyStack Overflow