C# ADO.net query runs slow

Question

I have a query that .net builds and executes via ADO to Sql Server 2012. If ADO executes the query it times out waiting for the database to return but if I copy and past it into SQL Management studio and execute it against the same database it returns in less then a second.

I found that wrapping the query that ADO generates in ' storing it in a SQL variable and using SP_EXECUTESQL makes it run nice and fast. Why would there be a difference? I have not changed the query in any way.

Here is one of the queries it builds and executes (I left out all the code that dynamically builds the string that CommandText gets set to).

using(SqlConnection conn = ConnectionStringHelper.GetOpenConnection)
using(SqlCommand cmd = conn.CreateCommand)
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("agencyID", broadcastAgencyID);
    cmd.Parameters.AddWithValue("tableName", mapping.TableName);
    cmd.Parameters.AddWithValue("schemaName", mapping.SchemaName);
    cmd.Parameters.AddWithValue("broadcastEntityMappingID", mapping.BroadcastEntityMappingID);

    cmd.CommandText = @"SET NOCOUNT ON; 
        DECLARE @currentAgencyID NVARCHAR(MAX)
        DECLARE @currentFacilityID NVARCHAR(MAX)

        DECLARE @currentAgencyEntityBroadcastID INT
        SELECT broadcastEntity.AgencyID, broadcastEntity.FacilityID
        INTO #missingBroadcasts
        FROM [Resource].[AgencyFacility] broadcastEntity
        LEFT JOIN(
            SELECT keys.AgencyID, keys.FacilityID
                , record.BroadcastAgencyID
                , record.AgencyEntityBroadcastID
            FROM [Propagation].[AgencyEntityBroadcast] record
            INNER JOIN (
                SELECT AgencyEntityBroadcastID
                    ,[AgencyID], [FacilityID]
                FROM (
                    SELECT AgencyEntityBroadcastID
                        ,ColumnName AS [PropagationColumnName]
                        ,ColumnValue AS [PropagationColumnValue]
                    FROM Propagation.AgencyEntityBroadcastKeys
                    ) Keys
                PIVOT(MAX(PropagationColumnValue) FOR PropagationColumnName IN (
                            [AgencyID], [FacilityID]
                            )) pivoted
                ) keys ON keys.AgencyEntityBroadcastID = record.AgencyEntityBroadcastID
                WHERE record.BroadcastAgencyID = @agencyID
                    AND record.BroadcastEntityMappingID = @broadcastEntityMappingID
            ) keys ON keys.BroadcastAgencyID = broadcastEntity.AgencyID
                AND keys.AgencyID = broadcastEntity.AgencyID
            AND keys.FacilityID = broadcastEntity.FacilityID
        WHERE broadcastEntity.AgencyID = @agencyID
            AND keys.AgencyEntityBroadcastID IS NULL
        DECLARE entity_cursor CURSOR FOR
        SELECT * FROM #missingBroadcasts

        OPEN entity_cursor
        FETCH NEXT FROM entity_cursor
        INTO @currentAgencyID, @currentFacilityID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO Propagation.AgencyEntityBroadcast(
                BroadcastAgencyID,
                BroadcastEntityMappingID,
                BroadcastTypeID,
                CreatedOn,
                ModifiedOn
            )
            VALUES(
                @agencyID,
                @broadcastEntityMappingID,
                1,
                GETUTCDATE(),
                GETUTCDATE()
            )

            SET @currentAgencyEntityBroadcastID = (SELECT SCOPE_IDENTITY())
            INSERT INTO Propagation.AgencyEntityBroadcastKeys(
                AgencyEntityBroadcastID,
                ColumnName,
                ColumnValue
            ) VALUES (
                @currentAgencyEntityBroadcastID,
                'AgencyID',
                @currentAgencyID
            )
        INSERT INTO Propagation.AgencyEntityBroadcastKeys(
            AgencyEntityBroadcastID,
            ColumnName,
            ColumnValue
        ) VALUES (
            @currentAgencyEntityBroadcastID,
            'FacilityID',
            @currentFacilityID
        )

        FETCH NEXT FROM entity_cursor INTO @currentAgencyID, @currentFacilityID
        END

        CLOSE entity_cursor
        DEALLOCATE entity_cursor

        DROP TABLE #missingBroadcasts

        SET NOCOUNT OFF;";

    cmd.ExecuteNonQuery();
}

Be careful not to use `nvarchar` parameters against `varchar` columns as that will cause an implicit cast, read also http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms/7638059#7638059 — Tim Schmelter, May 23 '14 at 14:51
Our application is international so we use nvarchar for all of our columns. — Ranger1230, May 23 '14 at 14:54
Show your code anyway. It's important how you pass the parameters and which types you use. If the query is too large you can abbreviate it and show only the relevant part of the query and the ADO.NET code (f.e. some parameters). — Tim Schmelter, May 23 '14 at 14:55
Alright I updated it with one of the queries it generates. I took the result of the StringBuilder it used to build the CommandText since that is about 200 lines of code. This is basically how the SqlCommand is built and executed. — Ranger1230, May 23 '14 at 15:18
Why don't you create a stored-procedure and call that? Set the `SqlCommand.CommandType` to `CommandType.StoredProcedure`. — Tim Schmelter, May 23 '14 at 15:19
I would have to make a SPROC that does what the ton of lines of .NET code before the using statements do to builds the string. The string is dynamically built so the structure is all that is consistent each time I execute it. the number of inserts and the table it selects from for the temp table are all dynamic. — Ranger1230, May 23 '14 at 15:22
Post both actual query plans as XML and compare them (disable the timeout to get the slow plan). They might show conversion warnings in the slow version. — usr, May 23 '14 at 15:32
Yeah. That is totally not an ADO.NET issue - forget that stuff and focus on the SQL Side only. We do not care HOW you construct the statement - we have to help you speed up the statement. — TomTom, May 23 '14 at 15:36
Alright I'll work on getting that beast into a SPROC. Thanks all for the advice. — Ranger1230, May 23 '14 at 15:48
Possibly relevant reading ["Slow in the Application, Fast in SSMS? : Understanding Performance Mysteries"](http://www.sommarskog.se/query-plan-mysteries.html) — Scott Chamberlain, May 23 '14 at 15:59
No need for that. Ad hoc SQL is capable of achieving the same level of performance. Just capture the plans using SQL Profiler. There is an XML Showplan event. Or, capture the queries and reexecute them in SSMS. — usr, May 23 '14 at 16:02
Well the article Scott provided it did explain why wrapping the whole thing to be executed using SP_EXECUTESQL solves the performance problem. — Ranger1230, May 23 '14 at 16:54
Ok. Just want to make sure you are not converting everything to use sprocs now because it very likely not required to solve the problem. The fix is probably rather simple once the actual issue is identified. — usr, May 23 '14 at 17:21

score 0 · Accepted Answer · answered Feb 09 '15 at 10:39

This kind of behaviour is usually caused by parameter sniffing problems. You can try with option recompile or optimize for unknown .

Option recompile

....
WHERE broadcastEntity.AgencyID = @agencyID
            AND keys.AgencyEntityBroadcastID IS NULL
OPTION (RECOMPILE)

Optimize for unknown

.....
WHERE broadcastEntity.AgencyID = @agencyID
      AND keys.AgencyEntityBroadcastID IS NULL
OPTION (OPTIMIZE FOR (@agencyID UNKNOWN, @broadcastEntityMappingID UNKNOWN))

C# ADO.net query runs slow

1 Answers1

Option recompile

Optimize for unknown