All geek questions in one place

The same SQL query, when launched with sp_executesql, is fast, very slow if it is executed as a dynamic query in the query analyzer, why?

I have a query Linqthat works “fast” on my development server and is very slow on a production server (2 seconds locally versus 3 minutes on production). When trying to research and compare the execution plans of these two, I decided to "deploy" the Linq query from the operator sp_executesqland run it as a dynamic query in a local (fast) environment.

Now the same server, the same request, but one wrapped inside sp_executesqlanother speaker, has a much larger gap during operation: the first works in 2 seconds, the last takes 14 minutes.

I read this article http://technet.microsoft.com/en-au/library/cc966425.aspx where it explains how to sp_executesqluse an executable caching plan, will it make a difference of 13 minutes and 58 seconds?

I believe that posting actual queries may not be relevant (and look ugly and confusing), however, I put them in here if this can give more insight.

First do it 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 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)

Thank.

Edit (add explanation of solution):

I rewrote the Linq expression to create a more compact query with fewer internal samples, which resulted in a dynamic Query from 14 minutes to 35 seconds, which still left a 33-second difference between the one that works with sp_executesql and dynamic.

, :

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

.

Production, Production and Development . , , sp_executesql, , , !? (, , , )

, . , .

+3
2

, . , , , 30 .

0

, .

, ( sp_executesql)

.

, , .

+4

Source: https://habr.com/ru/post/1795250/

More articles:


All Articles