与SSMS相比,通过实体框架调用时,存储过程正在执行不同的索引 [英] Stored procedure is executing with different indexes when called via Entity Framework compared to SSMS
问题描述
我们有一个存储过程,通过 sp_executesql
执行动态sql。
我们已经通过SQL Server分析器并查看执行计划,分析器显示,当通过SSMS(SQL Server Management Studio)调用此过程时,它将使用良好的索引组合,因此在2秒内返回。
另一方面,当我们通过.NET应用程序(通过实体框架调用)调用此过程时,与SSMS中的调用相比,使用完全相同的参数 - 然后SQL Server对索引使用更糟糕的选择。
这个行为是确定性的。通过我们的应用程序调用 - 坏的索引。通过SSMS调用 - 好的索引。
有没有人知道为什么会这样?
谢谢。
更新:
存储过程调用如下所示:
EXEC [schema]。[sp] @ a = 123,@ b = NULL
程序内的SQL执行如下:
`EXEC sp_executesql
@sql,
N'@一个int,@b smallint',
@a = @a,
@b = @ b`
我找到答案:我在实体框架上集中了两点,但是当我扩大了我的搜索时,我发现ADO.NET在连接上使用不同的设置,因此使用不同的执行计划:为什么有些sql查询时使用sql查询更慢?
We have a stored procedure that executes dynamic sql via sp_executesql
.
We have observed via the SQL Server profiler and looking at the execution plans the profiler shows that when this procedure is called via SSMS (SQL Server Management Studio) it uses a good combination of indexes, therefore returning in 2 secs.
On the other hand, when we call this procedure via our .NET application (called via Entity Framework) - and exactly the same parameters are used compared to the call in SSMS - then SQL Server uses a much worse choice for the indexes.
And this behaviour is deterministic. Called via our app - bad index. Called via SSMS - good index.
Does anybody have an idea why this could be the case?
Many thanks.
Update:
The stored procedure call looks like this:
EXEC [schema].[sp] @a=123,@b=NULL
The dynamic SQL inside the procedure is executed like this:
`EXEC sp_executesql
@sql,
N'@a int, @b smallint',
@a = @a,
@b = @b`
I found the answer: I focused two much on entity framework, but when I widened my search I found answers about ADO.NET using different settings on the connection, therefore using a different execution plan: Why is some sql query much slower when used with SqlCommand?
这篇关于与SSMS相比,通过实体框架调用时,存储过程正在执行不同的索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!