When using QueryMultiple from dapper, are all of the queries run multiple times on the DB?

.Net core code example:

        var query1 = "select * from [User]";
        var query2 = "select * from UserRole";

        var multiQuery = $"{query1}; {query2}";

        using (var multi = await _dbConnection.QueryMultipleAsync(multiQuery))
        {
            var userResponse = multi.Read<UserResponse>();
            var userRoleResponse = multi.Read<UserRoleResponse>();

            //DO SOMETHING
        }

When I look at the queries run on the DB, I see the following:

SQL query to see latest queries in DB:

SELECT top 10 deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

I was expecting to see the following:

Does this mean that Dapper's QueryMultiple is running all queries multiple times?

Just hoping to understand how this works as we are looking at potential performance impacts.

Database is Sql Server.

CodePudding user response：

Does this mean that Dapper's QueryMultiple is running all queries multiple times?

No, you're misreading the DMVs.

sys.dm_exec_query_stats has one row per query, but sys.dm_exec_sql_text returns the whole batch (or stored procedure body), not an individual query. So you must use the statement_start_offset and statement_end_offset to extract the individual query.

Here's the example from the docs:

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2)   1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2)   1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;