SqlCommand.ExecuteReader became very slow

Question

I am using this code, to call an SQL function, which returns entries from an SQL Server database table

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();

The table I am working with contains a bit more than 1.5 million entries and should return a bit more than 250k entries.

If I am executing the SQL function within SSMS it needs 8 seconds to return the results and I already used the code above last week to get the results within my desktop application. At this time everything was fine. The code needed between 10-12 seconds to get the results.

The strange thing is, that today the code needs more than 40 seconds to return the same results, but I haven't change anything within the SQL function or the code itself. The only change I did in my programm is adding a few more classes, which have nothing to do with the code above.

If I am debugging the code, I can see, that the line

IDataReader reader = command.ExecuteReader();

needs the most of the time now.

Since I haven't change anything in the SQL function or the code itself, I can't understand why it is taking so long now...

And if needed, here is the SQL function, I am using:

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)
)

Thank you for any suggestions!

DB101 · Accepted Answer

This seems like a case of parameter sniffing

One thing that you could do is rewrite your procedure as follows:

`

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)
)

` You can then get you data as you would by calling a stored procedure.

SqlCommand.ExecuteReader became very slow

Answers (2)

Related Questions