c# - EF 和表值函数性能

Question

我们使用 Entity Framework 5.0.0，数据库优先方法。

我已经将几个内联表值函数 (ITVF) 导入到我的 EDMX 中，它们返回 IQueryable。一旦返回，我们通常会对它们进行一些类型的操作，比如 .Skip().Take()

性能良好，至少持续几个小时。如果不做任何事情(至少据我所知)，性能会在一夜之间下降。

起初，查询将花费大约 500 毫秒来执行。一夜之间，性能下降到查询返回超时异常(30-60 秒)的水平。这仅在从代码执行表函数时发生，例如:

IQueryable<MyResult> results =
            _context.MyTableFunction("searchforthis")
                .Skip(currentPagingPosition*20).Take(20);

(这不仅仅是我们第一次运行查询时发生的)

但是，如果我们直接从 SQL Server Management Studio 运行相同的表函数，查询将在大约 500 毫秒内执行。

如果我像这样“重新保存”表格函数:

ALTER FUNCTION [dbo].[MyTableFunction]......

无需进行任何“真正的”更改，即使从代码中调用，它也会立即恢复到 500 毫秒的速度。

我是否遗漏了一些明显的东西，有什么想法吗？

Answer 1

所以我尝试将 Option Recompile 添加到内联 TVF，但它似乎是不允许的。我发现的唯一方法是创建一个多语句函数，但它们有其缺点:

设置

CREATE Table MyTest
(
    ID INT PRIMARY KEY Identity,
    SomeValue VARCHAR(MAX),
    UpdateDate Date
)
CREATE INDEX IDX_MyTest_UpdateDate
 ON MyTest(UpdateDate)


GO

创建函数

 CREATE FUNCTION MyFunction(@FromDate date, @ToDate Date)
 RETURNS TABLE 
 AS
RETURN
(   
    SELECT SomeValue, UpdateDate
    FROM MyTest
    WHERE   UpdateDate >= @FromDate AND
            UpdateDate <=@ToDate
    -- OPTION (RECOMPILE) **** NOT ALLOWED HERE**
)
GO

CREATE FUNCTION MyFunction2(@FromDate date, @ToDate Date)
 RETURNS @retMyFunction TABLE
    (
        SomeValkue VARCHAR(MAX),
        UpdateDate Date
    ) 
 AS
BEGIN
    INSERT INTO @retMyFunction
    SELECT SomeValue, UpdateDate
    FROM MyTest
    WHERE   UpdateDate >= @FromDate AND
            UpdateDate <=@ToDate
    OPTION (RECOMPILE);

    RETURN;
END
GO

加载数据

 INSERT INTO MyTest (SomeValue, UpdateDate)
 SELECT CAST(X.n * Y.n * z.n AS VARCHAR(Max)), DATEADD(D, ROW_NUMBER() OVER (ORDER BY X.n), '2010-01-01')
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) X(n)
 CROSS APPLY (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) Y(n)
 CROSS APPLY (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) Z(n)

测试

-- Index Scan
SELECT * FROM MyTest
 WHERE UpdateDate > '2010-01-02' AND UpdateDate < '2020-01-01'
-- Index Seek
 SELECT * FROM MyTest
 WHERE UpdateDate > '2010-01-02' AND UpdateDate < '2010-01-05'


-- Index Scan
SELECT *
FROM MyFunction('2010-01-02', '2020-01-01')
-- Index Scan
SELECT *
FROM MyFunction('2010-01-02', '2010-01-05')


-- Index Scan
SELECT *
FROM MyFunction2('2010-01-02', '2020-01-01')
-- Index Seek
SELECT *
FROM MyFunction2('2010-01-02', '2010-01-05')