sql-server - SqlCommand.ExecuteReader 变得很慢

Question

我正在使用这段代码来调用 SQL 函数，该函数从 SQL Server 数据库表中返回条目

string cmd = String.Format("select * from dbo.GetData(@userId, @fileId, @created);");

using (SqlConnection conn = new SqlConnection(connectionString))
{
    if (conn.State != ConnectionState.Open)
        conn.Open();

    SqlCommand command = new SqlCommand(cmd, conn);

    if (String.IsNullOrEmpty(userId))
        command.Parameters.AddWithValue("@userId", DBNull.Value);
    else
        command.Parameters.AddWithValue("@userId", userId);

    if (String.IsNullOrEmpty(fileId))
        command.Parameters.AddWithValue("@fileId", DBNull.Value);
    else
        command.Parameters.AddWithValue("@fileId", docId);

    command.Parameters.AddWithValue("@created", created);

    internalWatch.Reset();
    internalWatch.Start();

    IDataReader reader = command.ExecuteReader();                
    table = GetDataTableFromDataReader(reader);

    reader.Close();
    reader.Dispose();

    conn.Close();

    internalWatch.Stop();

我正在使用的表包含超过 150 万个条目，应该返回超过 25 万个条目。

如果我在 SSMS 中执行 SQL 函数，它需要 8 秒才能返回结果，我上周已经使用上面的代码在我的桌面应用程序中获取结果。这时一切都很好。代码需要 10-12 秒才能得到结果。

奇怪的是，今天的代码需要 40 多秒才能返回相同的结果，但我没有更改 SQL 函数或代码本身的任何内容。我在我的程序中所做的唯一更改是添加了几个类，这与上面的代码无关。

如果我正在调试代码，我可以看到那行

IDataReader reader = command.ExecuteReader();

现在大部分时间都需要。

由于我没有更改 SQL 函数或代码本身的任何内容，所以我不明白为什么现在要花这么长时间...

如果需要，这里是 SQL 函数，我正在使用:

ALTER FUNCTION [dbo].[GetData]
(@userId varchar(128) = NULL, 
 @fileId varchar(192) = NULL, 
 @created DateTimeOffset(7))
RETURNS TABLE 
AS
    RETURN (
        WITH FindNewestVersion AS
        (
            SELECT
                *, 
                ROW_NUMBER() OVER (PARTITINO BY FileId, UserId 
                                   ORDER BY created DESC) rn 
            FROM
                table1
        )
        SELECT        
            q.Created, q.Updated, q.FileId, q.UserId, 
            F.column1, F.column2, F.column3
        FROM            
            table2 AS F 
        INNER JOIN
            table1 AS q ON F.column4 = q.PersonId AND F.created = q.created
        INNER JOIN
            (SELECT 
                 created, PersonId, DocumentId 
             FROM
                 FindNewestVersion 
             WHERE
                 rn = 1) AS x ON q.created = x.created 
                              AND q.PersonId = x.PersonId 
                              AND q.FileId = x.FileId

        WHERE        
            (F.column1 = 'Sample') 
            AND (q.Created <= @created) 
            AND (q.Updated >= @created)
            AND Q.PersonId = ISNULL(@userId, Q.PersonId)
            AND Q.FileId = ISNULL(@fileId, Q.FileId)
)

感谢您的任何建议!

Answer 1

这看起来像是参数嗅探的情况

您可以做的一件事是按如下方式重写您的过程:

`

DROP FUNCTION [dbo].[GetData]
CREATE PROCEDURE[dbo].[GetData]

(
@userId varchar(128) = NULL, 
@fileId varchar(192) = NULL, 
@created DateTimeOffset(7)
)

RETURNS TABLE 
AS
DECLARE @l_userId varchar(128) = NULL, 
DECLARE @l_fileId varchar(192) = NULL, 
DECLARE @l_created DateTimeOffset(7)

SET @l_userId = userId
SET @l_fileId = fileId
SET @l_created = @created



(

WITH FindNewestVersion as
(
    Select *, ROW_NUMBER() 
    over (partition by FileId, UserId ORDER BY created DESC)rn from table1
)


SELECT        q.Created, q.Updated, q.FileId, q.UserId, 
              F.column1, F.column2, F.column3
FROM            table2 AS F INNER JOIN
                table1 AS q
                ON F.column4 = q.PersonId AND F.created = q.created
                INNER JOIN
                (
                    select created, PersonId, DocumentId from FindNewestVersion where rn = 1
                ) AS x ON q.created = x.created AND q.PersonId = x.PersonId AND q.FileId = x.FileId

WHERE        (F.column1 = 'Sample') AND (q.Created <= @created) AND (q.Updated >= @created)
And Q.PersonId = ISNULL(@l_userId, Q.PersonId)
And Q.FileId = ISNULL(@l_fileId, Q.FileId)
)

`然后，您可以像调用存储过程一样获取数据。