Azure SQL中参数化查询NVARCHAR(4000)的性能较慢

Question

我注意到在使用Azure SQL数据库时，使用EntityFramework生成的参数化查询存在性能差异。

我有一张带有varchar(30)主键的表格，当我尝试使用主键从该表格中获取值时，EntityFramework创建了一个参数化查询，其中NVARCHAR(4000)作为数据类型。

DECLARE @p__linq__0 nvarchar(4000)
SET @p__linq__0 ='ehenurqp0kpql76kjsw3'

select * from mediacontentreferences where mediacontentreferenceid=@p__linq__0

这会在Azure SQL数据库上生成一个非常奇怪的执行计划。

由于使用全表扫描的索引扫描，效率相当低。

如果我使用正确数据类型的参数，执行计划将使用主键索引。

DECLARE @p__linq__1 varchar(30)
SET @p__linq__1 ='ehenurqp0kpql76kjsw3'

select * from mediacontentreferences where mediacontentreferenceid=@p__linq__1

如果我在本地SQL服务器上使用第一个查询，执行计划将NVARCHAR（4000）转换为VARCHAR（30），并使用主键索引。 这看起来像是Azure SQL服务器执行计划计算中的缺陷。

是否有可能更改Entity Framework创建查询的行为？

我已经阅读了一些关于此问题的文章，但没有找到解决方案。 为什么Code First / EF在原始SQL命令中使用'nvarchar（4000）'字符串？ 为什么Entity Framework生成大型参数？如何减少它们？

- Dan

---

本地机器上运行的 SQL Server 版本是什么？ - Grant Fritchey

在列中使用hasmaxlength设置吗？https://prashantbrall.wordpress.com/2011/04/ - Grant Fritchey

本地部署，已测试2008 R2和2014，两者均可。 - Dan

列的长度没有影响，但如果我将参数类型更改为varchar（与列相同），则在Azure上无论长度如何都可以工作。 - Dan

我问这个问题的原因是因为Azure优化器更加先进，或者略领先于SQL Server 2017。因此，将2014年或之前的行为与Azure进行比较并不会非常准确。 - Grant Fritchey

Answer 1

您必须为列指定正确的数据类型，然后EF在生成查询时会使用正确的数据类型声明参数。只需将此添加到您的DbContext：

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<mediacontentreferences >().Property(e => e.mediacontentreferenceid).HasColumnType("varchar");
    }

- Jan Muncinsky

---

是的，这将修复EntityFramework生成的实际查询（谢谢），但它并没有回答为什么Azure SQL Server在提供的查询上使用那个“有创意”的执行计划。 - Dan

@Dan，这与Azure无关。问题在于如果您使用varchar数据类型定义列，但在查询中声明参数为nvarchar，则SQL服务器必须进行类型转换并无法使用索引。 - Jan Muncinsky

但是为什么不同的SQL服务器版本会提供不同的计划呢？它只会在数据类型不匹配且在Azure上运行时建议使用索引扫描吗？ - Dan

优化器在2014和Azure之间是不同的。2014仍未默认启用所有优化器更新（2016及更高版本已经启用）。Azure则已经启用了。我不会感到惊讶如果看到不同的行为。 - Grant Fritchey

我可以确认，SQL Server 2016也会出现相同的行为，所以这可能与“优化”有关。很难理解为什么它不能想出一个更聪明的计划来处理“select * from x where id=N'123'”而不是“select * from x where id='123'”。 - Dan