当前位置： 文江博客话题详情

使用 sp_executesql 运行相同的 SQL 查询时速度很快，如果在查询分析器中作为动态查询执行则非常慢，为什么？

发布于 2024-10-19 14:24:20 字数 10525 浏览 0 评论 0原文

我有一个 Linq 查询，它在我的开发服务器上运行“快速”，但在生产服务器上运行非常慢（本地运行 2 秒，生产运行 3 分钟）。 在尝试调查和比较两者的执行计划时，我决定将 Linq 查询从 sp_executesql 语句中“展开”，并将其作为本地（快速）环境上的动态查询运行。

现在，相同的服务器、相同的查询，但一个包含在 sp_executesql 中，另一个动态在运行时间上有更大的差距：前者在 2 秒内运行，后者需要 14 分钟。

我正在读这篇文章 http://technet.microsoft.com/en-au/library/cc966425.aspx 其中解释了 sp_executesql 如何使用缓存的执行计划，这会弥补 13 分钟和 58 秒的差异吗？

我相信发布实际查询可能无关紧要（并且看起来丑陋且令人费解），但是，如果可以提供更多见解，我将它们粘贴到此处。

第一个，在 2 秒内执行：

exec sp_executesql N'SELECT [t0].[id] AS [Id], (CONVERT(Float,[dbo].[RankWords]((
    SELECT [t18].[searchable_1]
    FROM (
        SELECT TOP (1) [t17].[searchable_1]
        FROM [dbo].[contents] AS [t17]
        WHERE [t17].[navigation_tree_id] = [t0].[id]
        ) AS [t18]
    ), @p12, @p13, @p14))) + (CONVERT(Float,[dbo].[RankWords]((
    SELECT [t20].[name]
    FROM (
        SELECT TOP (1) [t19].[name]
        FROM [dbo].[contents] AS [t19]
        WHERE [t19].[navigation_tree_id] = [t0].[id]
        ) AS [t20]
    ), @p15, @p16, @p17))) AS [GlobalRank], [t0].[modified_date] AS [ModifiedDate], [t0].[parent_id] AS [ParentId], [t0].[template_id] AS [TemplateId], (
    SELECT [t22].[name]
    FROM (
        SELECT TOP (1) [t21].[name]
        FROM [dbo].[contents] AS [t21]
        WHERE [t21].[navigation_tree_id] = [t0].[id]
        ) AS [t22]
    ) AS [Name], [t0].[id] AS [id2], [t0].[uri], [t0].[site_id], [t0].[list_order], [t0].[pointed_node_id], [t0].[start_date], [t0].[expiry_date], [t0].[is_external_link], [t0].[priority_level], [t0].[is_active], [t0].[power_level_required]
FROM [dbo].[navigation_trees] AS [t0]
WHERE (((
    SELECT COUNT(*)
    FROM [dbo].[label__navigation_tree] AS [t1]
    WHERE [t1].[navigation_tree_id] = [t0].[id]
    )) > @p0) AND ([t0].[template_id] IS NOT NULL) AND (([t0].[template_id]) IN (@p1, @p2, @p3)) AND (EXISTS(
    SELECT TOP (1) NULL AS [EMPTY]
    FROM [dbo].[contents] AS [t2]
    WHERE [t2].[navigation_tree_id] = [t0].[id]
    )) AND (((CONVERT(Bit,[dbo].[HasMatch](
    (CASE 
        WHEN ((
            SELECT [t4].[name]
            FROM (
                SELECT TOP (1) [t3].[name]
                FROM [dbo].[contents] AS [t3]
                WHERE [t3].[navigation_tree_id] = [t0].[id]
                ) AS [t4]
            )) IS NOT NULL THEN CONVERT(NVarChar(MAX),(
            SELECT [t6].[name]
            FROM (
                SELECT TOP (1) [t5].[name]
                FROM [dbo].[contents] AS [t5]
                WHERE [t5].[navigation_tree_id] = [t0].[id]
                ) AS [t6]
            ))
        WHEN (@p4 + ((
            SELECT [t8].[title]
            FROM (
                SELECT TOP (1) [t7].[title]
                FROM [dbo].[contents] AS [t7]
                WHERE [t7].[navigation_tree_id] = [t0].[id]
                ) AS [t8]
            ))) IS NOT NULL THEN CONVERT(NVarChar(MAX),@p5 + ((
            SELECT [t10].[title]
            FROM (
                SELECT TOP (1) [t9].[title]
                FROM [dbo].[contents] AS [t9]
                WHERE [t9].[navigation_tree_id] = [t0].[id]
                ) AS [t10]
            )))
        WHEN (@p6 + ((
            SELECT [t12].[description]
            FROM (
                SELECT TOP (1) [t11].[description]
                FROM [dbo].[contents] AS [t11]
                WHERE [t11].[navigation_tree_id] = [t0].[id]
                ) AS [t12]
            ))) IS NOT NULL THEN @p7 + ((
            SELECT [t14].[description]
            FROM (
                SELECT TOP (1) [t13].[description]
                FROM [dbo].[contents] AS [t13]
                WHERE [t13].[navigation_tree_id] = [t0].[id]
                ) AS [t14]
            ))
        ELSE CONVERT(NVarChar(MAX),@p8)
     END), @p9))) = 1) OR ((CONVERT(Bit,[dbo].[HasMatch]((
    SELECT [t16].[searchable_1]
    FROM (
        SELECT TOP (1) [t15].[searchable_1]
        FROM [dbo].[contents] AS [t15]
        WHERE [t15].[navigation_tree_id] = [t0].[id]
        ) AS [t16]
    ), @p10))) = 1)) AND ([t0].[pointed_node_id] IS NULL) AND ([t0].[site_id] = @p11) AND ([t0].[is_active] = 1)',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 nvarchar(4000),@p5 nvarchar(1),@p6 nvarchar(4000),@p7 nvarchar(1),@p8 nvarchar(4000),@p9 nvarchar(3),@p10 nvarchar(3),@p11 int,@p12 nvarchar(3),@p13 int,@p14 bit,@p15 nvarchar(3),@p16 int,@p17 bit',@p0=0,@p1=158,@p2=159,@p3=160,@p4=N'',@p5=N' ',@p6=N'',@p7=N' ',@p8=N'',@p9=N'actor',@p10=N'actor',@p11=15,@p12=N'actor',@p13=3,@p14=0,@p15=N'actor',@p16=3,@p17=0

第二个在 14 分钟内执行：

DECLARE @p0 int,@p1 int,@p2 int,@p3 int,@p4 nvarchar(4000),@p5 nvarchar(1),@p6 nvarchar(4000),@p7 nvarchar(1),@p8 nvarchar(4000),@p9 nvarchar(3),@p10 nvarchar(3),@p11 int,@p12 nvarchar(3),@p13 int,@p14 bit,@p15 nvarchar(3),@p16 int,@p17 bit

SET @p0=0
SET @p1=158
SET @p2=159
SET @p3=160
SET @p4=N''
SET @p5=N' '
SET @p6=N''
SET @p7=N' '
SET @p8=N''
SET @p9=N'actor'
SET @p10=N'actor'
SET @p11=15
SET @p12=N'actor'
SET @p13=3
SET @p14=0
SET @p15=N'actor'
SET @p16=3
SET @p17=0

SELECT 
    [t0].[id] AS [Id], 
    (
        CONVERT(Float,[dbo].[RankWords]
        ((
            SELECT [t18].[searchable_1]
            FROM (
                SELECT TOP (1) [t17].[searchable_1]
                FROM [dbo].[contents] AS [t17]
                WHERE [t17].[navigation_tree_id] = [t0].[id]
                ) AS [t18]
            ), 
            @p12, 
            @p13, 
            @p14
        ))
    ) + 
    (
        CONVERT(Float,[dbo].[RankWords]((
            SELECT [t20].[name]
            FROM (
                SELECT TOP (1) [t19].[name]
                FROM [dbo].[contents] AS [t19]
                WHERE [t19].[navigation_tree_id] = [t0].[id]
                ) AS [t20]
            ), 
            @p15, 
            @p16, 
            @p17
        ))
    ) 
    AS [GlobalRank], 
    [t0].[modified_date] AS [ModifiedDate], 
    [t0].[parent_id] AS [ParentId], 
    [t0].[template_id] AS [TemplateId], 
    (
        SELECT [t22].[name]
        FROM (
            SELECT TOP (1) [t21].[name]
            FROM [dbo].[contents] AS [t21]
            WHERE [t21].[navigation_tree_id] = [t0].[id]
            ) AS [t22]
    ) AS [Name], 
    [t0].[id] AS [id2], 
    [t0].[uri], 
    [t0].[site_id], 
    [t0].[list_order], 
    [t0].[pointed_node_id], 
    [t0].[start_date], 
    [t0].[expiry_date], 
    [t0].[is_external_link], 
    [t0].[priority_level], 
    [t0].[is_active], 
    [t0].[power_level_required]

FROM [dbo].[navigation_trees] AS [t0]
WHERE 

    (
        ((
        SELECT COUNT(*)
        FROM [dbo].[label__navigation_tree] AS [t1]
        WHERE [t1].[navigation_tree_id] = [t0].[id]
        )) > @p0
    ) 
    AND 
    ([t0].[template_id] IS NOT NULL) 
    AND 
    (([t0].[template_id]) IN (@p1, @p2, @p3)) 
    AND 
    (EXISTS(
        SELECT TOP (1) NULL AS [EMPTY]
        FROM [dbo].[contents] AS [t2]
        WHERE [t2].[navigation_tree_id] = [t0].[id]
    )) 
    AND 
    (
        ((
            CONVERT(Bit,[dbo].[HasMatch](
        (CASE 
            WHEN ((
                SELECT [t4].[name]
                FROM (
                    SELECT TOP (1) [t3].[name]
                    FROM [dbo].[contents] AS [t3]
                    WHERE [t3].[navigation_tree_id] = [t0].[id]
                    ) AS [t4]
                )) IS NOT NULL 
            THEN 
                CONVERT(NVarChar(MAX),
                (
                    SELECT [t6].[name]
                    FROM (
                        SELECT TOP (1) [t5].[name]
                        FROM [dbo].[contents] AS [t5]
                        WHERE [t5].[navigation_tree_id] = [t0].[id]
                        ) AS [t6]
                    )               
                )
            WHEN 
                (@p4 + ((SELECT [t8].[title]
                            FROM (
                                SELECT TOP (1) [t7].[title]
                                FROM [dbo].[contents] AS [t7]
                                WHERE [t7].[navigation_tree_id] = [t0].[id]
                                ) AS [t8]
                            ))
                ) IS NOT NULL 
            THEN CONVERT(NVarChar(MAX),@p5 + ((
                SELECT [t10].[title]
                FROM (
                    SELECT TOP (1) [t9].[title]
                    FROM [dbo].[contents] AS [t9]
                    WHERE [t9].[navigation_tree_id] = [t0].[id]
                    ) AS [t10]
                )))
            WHEN (@p6 + ((
                SELECT [t12].[description]
                FROM (
                    SELECT TOP (1) [t11].[description]
                    FROM [dbo].[contents] AS [t11]
                    WHERE [t11].[navigation_tree_id] = [t0].[id]
                    ) AS [t12]
                ))) IS NOT NULL THEN @p7 + ((
                SELECT [t14].[description]
                FROM (
                    SELECT TOP (1) [t13].[description]
                    FROM [dbo].[contents] AS [t13]
                    WHERE [t13].[navigation_tree_id] = [t0].[id]
                    ) AS [t14]
                ))
            ELSE CONVERT(NVarChar(MAX),@p8)
         END), @p9))) = 1)

        OR ((CONVERT(Bit,[dbo].[HasMatch]((
        SELECT [t16].[searchable_1]
        FROM (
            SELECT TOP (1) [t15].[searchable_1]
            FROM [dbo].[contents] AS [t15]
            WHERE [t15].[navigation_tree_id] = [t0].[id]
            ) AS [t16]
        ), @p10))) = 1)

    ) AND ([t0].[pointed_node_id] IS NULL) AND ([t0].[site_id] = @p11) AND ([t0].[is_active] = 1)

谢谢。

编辑（添加解决方案的解释）：

我重写了 Linq 表达式，以生成一个具有更少内部选择的精简查询，结果将动态查询从 14 分钟缩短到 35 秒，而动态查询之间仍然存在 33 秒的差异一种是使用 sp_executesql 运行的，另一种是动态运行的。

然后，我使用执行计划索引建议创建以下索引：

CREATE NONCLUSTERED INDEX [JH_contents_navigation_tree_id]
ON [dbo].[contents] ([navigation_tree_id])


CREATE NONCLUSTERED INDEX [JH_label_navigation_tree_navigation_tree_id]
ON [dbo].[label__navigation_tree] ([navigation_tree_id])

CREATE NONCLUSTERED INDEX [JH_navigation_trees_sid_pnid_isactv_tmplid]
ON [dbo].[navigation_trees] ([site_id],[pointed_node_id],[is_active],[template_id])

这使两个查询的时间达到了几百毫秒。

这也解决了我的开发服务器和生产服务器之间的执行时间差异的另一个问题，但是，虽然我可以解释生产服务器和开发服务器之间的执行时间差异到服务器安装特性，但我仍然不明白为什么尽管基础表仍然缺少索引，但使用 sp_executesql 运行的同一服务器查询仍然非常快！？ （即使我已标记为已回答，也请添加评论，这会很有趣）

所有建议都帮助我缩小了问题范围，所以谢谢大家。然而，我觉得亚伦·肯普夫的回答结束了确定的实际问题。

原文

I've got a Linq query that runs "fast" on my development server and very slow on production server (2 seconds locally vs 3 minutes on production).
While trying to investigate and compare the execution plans of the two I decided to "unwrap" the Linq query out of the sp_executesql statement and run it as a dynamic query on the local (fast) environment.

Now same server, same query but one wrapped inside the sp_executesql the other dynamic have a much bigger gap in running time: the former runs in 2 seconds the latter takes 14 minutes.

I was reading this article
http://technet.microsoft.com/en-au/library/cc966425.aspx
where it explains how sp_executesql use cached executing plan, would that make up 13min and 58sec difference?

I believe posting the actual queries maybe irrelevant (and look ugly and convoluted) however, I paste them here if that can give more insight.

First, executing in 2 seconds:

exec sp_executesql N'SELECT [t0].[id] AS [Id], (CONVERT(Float,[dbo].[RankWords]((
    SELECT [t18].[searchable_1]
    FROM (
        SELECT TOP (1) [t17].[searchable_1]
        FROM [dbo].[contents] AS [t17]
        WHERE [t17].[navigation_tree_id] = [t0].[id]
        ) AS [t18]
    ), @p12, @p13, @p14))) + (CONVERT(Float,[dbo].[RankWords]((
    SELECT [t20].[name]
    FROM (
        SELECT TOP (1) [t19].[name]
        FROM [dbo].[contents] AS [t19]
        WHERE [t19].[navigation_tree_id] = [t0].[id]
        ) AS [t20]
    ), @p15, @p16, @p17))) AS [GlobalRank], [t0].[modified_date] AS [ModifiedDate], [t0].[parent_id] AS [ParentId], [t0].[template_id] AS [TemplateId], (
    SELECT [t22].[name]
    FROM (
        SELECT TOP (1) [t21].[name]
        FROM [dbo].[contents] AS [t21]
        WHERE [t21].[navigation_tree_id] = [t0].[id]
        ) AS [t22]
    ) AS [Name], [t0].[id] AS [id2], [t0].[uri], [t0].[site_id], [t0].[list_order], [t0].[pointed_node_id], [t0].[start_date], [t0].[expiry_date], [t0].[is_external_link], [t0].[priority_level], [t0].[is_active], [t0].[power_level_required]
FROM [dbo].[navigation_trees] AS [t0]
WHERE (((
    SELECT COUNT(*)
    FROM [dbo].[label__navigation_tree] AS [t1]
    WHERE [t1].[navigation_tree_id] = [t0].[id]
    )) > @p0) AND ([t0].[template_id] IS NOT NULL) AND (([t0].[template_id]) IN (@p1, @p2, @p3)) AND (EXISTS(
    SELECT TOP (1) NULL AS [EMPTY]
    FROM [dbo].[contents] AS [t2]
    WHERE [t2].[navigation_tree_id] = [t0].[id]
    )) AND (((CONVERT(Bit,[dbo].[HasMatch](
    (CASE 
        WHEN ((
            SELECT [t4].[name]
            FROM (
                SELECT TOP (1) [t3].[name]
                FROM [dbo].[contents] AS [t3]
                WHERE [t3].[navigation_tree_id] = [t0].[id]
                ) AS [t4]
            )) IS NOT NULL THEN CONVERT(NVarChar(MAX),(
            SELECT [t6].[name]
            FROM (
                SELECT TOP (1) [t5].[name]
                FROM [dbo].[contents] AS [t5]
                WHERE [t5].[navigation_tree_id] = [t0].[id]
                ) AS [t6]
            ))
        WHEN (@p4 + ((
            SELECT [t8].[title]
            FROM (
                SELECT TOP (1) [t7].[title]
                FROM [dbo].[contents] AS [t7]
                WHERE [t7].[navigation_tree_id] = [t0].[id]
                ) AS [t8]
            ))) IS NOT NULL THEN CONVERT(NVarChar(MAX),@p5 + ((
            SELECT [t10].[title]
            FROM (
                SELECT TOP (1) [t9].[title]
                FROM [dbo].[contents] AS [t9]
                WHERE [t9].[navigation_tree_id] = [t0].[id]
                ) AS [t10]
            )))
        WHEN (@p6 + ((
            SELECT [t12].[description]
            FROM (
                SELECT TOP (1) [t11].[description]
                FROM [dbo].[contents] AS [t11]
                WHERE [t11].[navigation_tree_id] = [t0].[id]
                ) AS [t12]
            ))) IS NOT NULL THEN @p7 + ((
            SELECT [t14].[description]
            FROM (
                SELECT TOP (1) [t13].[description]
                FROM [dbo].[contents] AS [t13]
                WHERE [t13].[navigation_tree_id] = [t0].[id]
                ) AS [t14]
            ))
        ELSE CONVERT(NVarChar(MAX),@p8)
     END), @p9))) = 1) OR ((CONVERT(Bit,[dbo].[HasMatch]((
    SELECT [t16].[searchable_1]
    FROM (
        SELECT TOP (1) [t15].[searchable_1]
        FROM [dbo].[contents] AS [t15]
        WHERE [t15].[navigation_tree_id] = [t0].[id]
        ) AS [t16]
    ), @p10))) = 1)) AND ([t0].[pointed_node_id] IS NULL) AND ([t0].[site_id] = @p11) AND ([t0].[is_active] = 1)',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 nvarchar(4000),@p5 nvarchar(1),@p6 nvarchar(4000),@p7 nvarchar(1),@p8 nvarchar(4000),@p9 nvarchar(3),@p10 nvarchar(3),@p11 int,@p12 nvarchar(3),@p13 int,@p14 bit,@p15 nvarchar(3),@p16 int,@p17 bit',@p0=0,@p1=158,@p2=159,@p3=160,@p4=N'',@p5=N' ',@p6=N'',@p7=N' ',@p8=N'',@p9=N'actor',@p10=N'actor',@p11=15,@p12=N'actor',@p13=3,@p14=0,@p15=N'actor',@p16=3,@p17=0

Second one executing in 14 minutes:

DECLARE @p0 int,@p1 int,@p2 int,@p3 int,@p4 nvarchar(4000),@p5 nvarchar(1),@p6 nvarchar(4000),@p7 nvarchar(1),@p8 nvarchar(4000),@p9 nvarchar(3),@p10 nvarchar(3),@p11 int,@p12 nvarchar(3),@p13 int,@p14 bit,@p15 nvarchar(3),@p16 int,@p17 bit

SET @p0=0
SET @p1=158
SET @p2=159
SET @p3=160
SET @p4=N''
SET @p5=N' '
SET @p6=N''
SET @p7=N' '
SET @p8=N''
SET @p9=N'actor'
SET @p10=N'actor'
SET @p11=15
SET @p12=N'actor'
SET @p13=3
SET @p14=0
SET @p15=N'actor'
SET @p16=3
SET @p17=0

SELECT 
    [t0].[id] AS [Id], 
    (
        CONVERT(Float,[dbo].[RankWords]
        ((
            SELECT [t18].[searchable_1]
            FROM (
                SELECT TOP (1) [t17].[searchable_1]
                FROM [dbo].[contents] AS [t17]
                WHERE [t17].[navigation_tree_id] = [t0].[id]
                ) AS [t18]
            ), 
            @p12, 
            @p13, 
            @p14
        ))
    ) + 
    (
        CONVERT(Float,[dbo].[RankWords]((
            SELECT [t20].[name]
            FROM (
                SELECT TOP (1) [t19].[name]
                FROM [dbo].[contents] AS [t19]
                WHERE [t19].[navigation_tree_id] = [t0].[id]
                ) AS [t20]
            ), 
            @p15, 
            @p16, 
            @p17
        ))
    ) 
    AS [GlobalRank], 
    [t0].[modified_date] AS [ModifiedDate], 
    [t0].[parent_id] AS [ParentId], 
    [t0].[template_id] AS [TemplateId], 
    (
        SELECT [t22].[name]
        FROM (
            SELECT TOP (1) [t21].[name]
            FROM [dbo].[contents] AS [t21]
            WHERE [t21].[navigation_tree_id] = [t0].[id]
            ) AS [t22]
    ) AS [Name], 
    [t0].[id] AS [id2], 
    [t0].[uri], 
    [t0].[site_id], 
    [t0].[list_order], 
    [t0].[pointed_node_id], 
    [t0].[start_date], 
    [t0].[expiry_date], 
    [t0].[is_external_link], 
    [t0].[priority_level], 
    [t0].[is_active], 
    [t0].[power_level_required]

FROM [dbo].[navigation_trees] AS [t0]
WHERE 

    (
        ((
        SELECT COUNT(*)
        FROM [dbo].[label__navigation_tree] AS [t1]
        WHERE [t1].[navigation_tree_id] = [t0].[id]
        )) > @p0
    ) 
    AND 
    ([t0].[template_id] IS NOT NULL) 
    AND 
    (([t0].[template_id]) IN (@p1, @p2, @p3)) 
    AND 
    (EXISTS(
        SELECT TOP (1) NULL AS [EMPTY]
        FROM [dbo].[contents] AS [t2]
        WHERE [t2].[navigation_tree_id] = [t0].[id]
    )) 
    AND 
    (
        ((
            CONVERT(Bit,[dbo].[HasMatch](
        (CASE 
            WHEN ((
                SELECT [t4].[name]
                FROM (
                    SELECT TOP (1) [t3].[name]
                    FROM [dbo].[contents] AS [t3]
                    WHERE [t3].[navigation_tree_id] = [t0].[id]
                    ) AS [t4]
                )) IS NOT NULL 
            THEN 
                CONVERT(NVarChar(MAX),
                (
                    SELECT [t6].[name]
                    FROM (
                        SELECT TOP (1) [t5].[name]
                        FROM [dbo].[contents] AS [t5]
                        WHERE [t5].[navigation_tree_id] = [t0].[id]
                        ) AS [t6]
                    )               
                )
            WHEN 
                (@p4 + ((SELECT [t8].[title]
                            FROM (
                                SELECT TOP (1) [t7].[title]
                                FROM [dbo].[contents] AS [t7]
                                WHERE [t7].[navigation_tree_id] = [t0].[id]
                                ) AS [t8]
                            ))
                ) IS NOT NULL 
            THEN CONVERT(NVarChar(MAX),@p5 + ((
                SELECT [t10].[title]
                FROM (
                    SELECT TOP (1) [t9].[title]
                    FROM [dbo].[contents] AS [t9]
                    WHERE [t9].[navigation_tree_id] = [t0].[id]
                    ) AS [t10]
                )))
            WHEN (@p6 + ((
                SELECT [t12].[description]
                FROM (
                    SELECT TOP (1) [t11].[description]
                    FROM [dbo].[contents] AS [t11]
                    WHERE [t11].[navigation_tree_id] = [t0].[id]
                    ) AS [t12]
                ))) IS NOT NULL THEN @p7 + ((
                SELECT [t14].[description]
                FROM (
                    SELECT TOP (1) [t13].[description]
                    FROM [dbo].[contents] AS [t13]
                    WHERE [t13].[navigation_tree_id] = [t0].[id]
                    ) AS [t14]
                ))
            ELSE CONVERT(NVarChar(MAX),@p8)
         END), @p9))) = 1)

        OR ((CONVERT(Bit,[dbo].[HasMatch]((
        SELECT [t16].[searchable_1]
        FROM (
            SELECT TOP (1) [t15].[searchable_1]
            FROM [dbo].[contents] AS [t15]
            WHERE [t15].[navigation_tree_id] = [t0].[id]
            ) AS [t16]
        ), @p10))) = 1)

    ) AND ([t0].[pointed_node_id] IS NULL) AND ([t0].[site_id] = @p11) AND ([t0].[is_active] = 1)

Thanks.

Edit (Adding explanation of solution):

I have rewritten the Linq expression to generate a leaner query with fewer inner selects, which as a result had taken down the dynamic Query from 14 minutes to 35 seconds that had still left a 33 seconds difference between the one running with the sp_executesql and the dynamic one.

I then used the Execution Plan index suggestion to create the following indexes:

CREATE NONCLUSTERED INDEX [JH_contents_navigation_tree_id]
ON [dbo].[contents] ([navigation_tree_id])


CREATE NONCLUSTERED INDEX [JH_label_navigation_tree_navigation_tree_id]
ON [dbo].[label__navigation_tree] ([navigation_tree_id])

CREATE NONCLUSTERED INDEX [JH_navigation_trees_sid_pnid_isactv_tmplid]
ON [dbo].[navigation_trees] ([site_id],[pointed_node_id],[is_active],[template_id])

which have brought the two queries at few hundreds milliseconds mark.

This has solved also my other problem of difference in execution time between my Development server and Production server, however, while I can explain the difference in execution time between the Production and Development server down to server installation idiosyncrasies I still don't understand why on the same server the query run with the sp_executesql was still very fast despite the underlying tables were still missing indexes!? (Please add comments even if I've marked as answered, that will be interesting to know)

All suggestion helped me to narrow down the problem, so thank you all. However I feel Aaron Kempf's answer closes identified the actual problem.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

别再吹冷风 2024-10-26 14:24:20

听起来好像使用了不同的计划，或者数据库中的数据不一样。

要获得新的查询计划，请尝试向动态添加一个空格（不是 sp_executesql）并重新运行它，

您也可以尝试更新统计信息。

如果您在加入的列上强制转换/转换数据类型，也可能会发生这种情况。

sounds like a different plan is used, or the data in the databases isnt the same.

to get a new query plan try to add a space to the dynamic (not sp_executesql) and re-run it

also you can try to update your statistics.

this can also happen if you cast/convert data types on columns that you join on.

回复 原文
江南烟雨〆相思醉 2024-10-26 14:24:20

我只是认为你应该考虑为正确的字段建立索引。谁在乎哪一个更快，除非您返回大量行，否则在过去十年中我没有遇到过任何查询花费超过 30 秒的情况。

I just think that you should look at indexing the correct fields. Who cares which one is faster, unless you're returning a HUGE amount of rows, I haven't had ANY query take more than 30 seconds in the past decade.

回复 原文
~没有更多了~

关于作者

他不在意

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

ni139999

文章 0 评论 0

Smile

文章 0 评论 0

木子李

文章 0 评论 0

仅此而已

文章 0 评论 0

qq_2gSKZM

文章 0 评论 0

内心激荡

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文