Clustered and non-clustered index seeking increase execution time in stored procedure

Question

I have a stored procedure which takes over 3 minutes to execute, when I show the execution plan I find

Clustered index seeking and non-clustered index seeking

My query:

SELECT distinct
    [tbl_worflowprocess].[currenttid]
    ,USR2.[firstname] AS [prev_action_user_name]
    ,USR3.[firstname] AS [current_action_user_name]
    ,COD2.[Code] AS [reasontext]
    ,[tbl_application_details].[application_id] AS [ApplicationId]
    ,[tbl_application_details].[application_number] AS [ApplicationNumber]
    ,[dbo].[fn_app_GetApplicationId]([tbl_application_details].[link_application_id]) AS [LinkApplicationId]
    ,[tbl_application_details].[link_type] AS [LinkType]
    ,[dbo].[fn_app_CountProductsInApplication]([tbl_application_details].[application_id]) AS [ProductsCount]
    ,[tbl_application_details].[submission_date] AS [SubmissionDate]
    ,[tbl_jurisdiction].[jurisdictionname]
    ,[tbl_devicetype].[devicetype]
    ,COD1.[Code] AS [ClassificationName]
    ,EST1.[name] AS [ApplicantName]
    ,EST2.[name] AS [ManufacturerName]
    ,[dbo].[fnGetApplicationStatusFromTaskId]([tbl_worflowprocess].[currenttid]) AS [AppStatus]
    ,[dbo].[fnGetApplicationStatusText](@pLoggedInUserRoleId,[tbl_worflowprocess].[currenttid]) AS [StatusText]
    ,[Paid] = (CASE [tbl_application_details].[paid] WHEN 1 THEN 'Yes' ELSE 'No' END)
    ,[CreationDate] = [tbl_worflowprocess].[creationdate]
    ,[CommentText] = 
                    (select CommentText 
                        from dbo.tbl_application_comments
                            where Id = (select max(Id) from dbo.tbl_application_comments
                                        where ApplicationId= [tbl_application_details].[application_id] and UserId = @pLoggedInUserID ))
    ,[LastCab] = (select isnull(dbo.fnGetLastCabForApplication([tbl_application_details].[application_id]),'-'))
    ,[tbl_application_details].[ArExpired]

FROM 
    [tbl_worflowprocess]
INNER JOIN
    (SELECT 
         [application_id], [actionbyuser_id],
         [actionbyrole_id], [reason_id], createddate 
     FROM 
         [tbl_applicationworkflowhistory]
     INNER JOIN
         (SELECT 
              [application_id] AS C1, MAX([version]) AS C2
          FROM 
              [tbl_applicationworkflowhistory]
          WHERE 
              (@pCurrentRoleId IS NULL 
               OR [application_id] IN (SELECT [application_id] 
                                       FROM [tbl_applicationworkflowhistory] 
                                       INNER JOIN [tbl_worflowprocess] ON [tbl_applicationworkflowhistory].[application_id] = [tbl_worflowprocess].[applicationid]
                                       WHERE 
                                           (@pSearchInHistory = 0 OR [tbl_applicationworkflowhistory].[actionbyrole_id] = @pCurrentRoleId OR [tbl_worflowprocess].[currentroleid] = @pCurrentRoleId)
                                        AND (@pSearchInHistory = 1 OR [tbl_worflowprocess].[currentroleid] = @pCurrentRoleId)
                                      )
                ) AND
                (@pCurrentUserId IS NULL OR [application_id] IN (
                                    SELECT [application_id] 
                                    FROM [tbl_applicationworkflowhistory] 
                                    INNER JOIN [tbl_worflowprocess] ON [tbl_applicationworkflowhistory].[application_id]=[tbl_worflowprocess].[applicationid]
                                    WHERE 
                                    (@pSearchInHistory=0 OR [tbl_applicationworkflowhistory].[actionbyuser_id] =@pCurrentUserId OR [tbl_worflowprocess].[currentuserid]=@pCurrentUserId)
                                        AND (@pSearchInHistory=1 OR [tbl_worflowprocess].[currentuserid]=@pCurrentUserId)
                                    )
                ) AND
                (@pCurrentEstablishmentId IS NULL OR [application_id] IN (
                                    SELECT [application_id] 
                                    FROM [tbl_applicationworkflowhistory] 
                                    INNER JOIN [tbl_worflowprocess] ON [tbl_applicationworkflowhistory].[application_id]=[tbl_worflowprocess].[applicationid]
                                    WHERE 
                                    (@pSearchInHistory=0 OR [tbl_applicationworkflowhistory].[actionbyuser_id] IN 
                                    (SELECT [user_id] FROM [tbl_user] WHERE [establishment_id]=@pCurrentEstablishmentId) OR [tbl_worflowprocess].[currentuserid] IN 
                                    (SELECT [user_id] FROM [tbl_user] WHERE [establishment_id]=@pCurrentEstablishmentId))
                                        AND (@pSearchInHistory=1 OR [tbl_worflowprocess].[currentuserid] IN 
                                        (SELECT [user_id] FROM [tbl_user] WHERE [establishment_id]=@pCurrentEstablishmentId))
                                    )
                )
        GROUP BY [application_id]
    )AS T1 ON ([tbl_applicationworkflowhistory].[application_id]=T1.C1 AND [tbl_applicationworkflowhistory].[version]=T1.C2)
) AS T2 ON([tbl_worflowprocess].[applicationid]=T2.[application_id])
INNER JOIN [tbl_application_details] ON [tbl_application_details].[application_id]=[tbl_worflowprocess].[applicationid]
INNER JOIN [tbl_user] USR1 ON USR1.[user_id]=[tbl_application_details].[responsible_user_id]
INNER JOIN [tbl_establishments] EST1 on EST1.[establishment_id] = USR1.[establishment_id]
LEFT OUTER JOIN [tbl_user] USR2 ON USR2.[user_id]=T2.[actionbyuser_id]
LEFT OUTER JOIN [tbl_user] USR3 ON USR3.[user_id]=[tbl_worflowprocess].[currentuserid]
LEFT OUTER JOIN [tbl_establishments] EST2 on EST2.[establishment_id] = [tbl_application_details].[manufacturer_id]
LEFT OUTER JOIN [tbl_jurisdiction] ON [tbl_jurisdiction].[jurisdiction_id]=[tbl_application_details].[jurisdiction_id]
LEFT OUTER JOIN [tbl_devicetype] ON [tbl_devicetype].[devicetype_id]=[tbl_application_details].[device_type_id]
LEFT OUTER JOIN [tbl_codes] COD1 ON COD1.[code_id]=[tbl_application_details].[device_classification_id]
LEFT OUTER JOIN [tbl_codes] COD2 ON COD2.[code_id]=T2.[reason_id]
LEFT OUTER JOIN [tbl_certificates] CERTF ON CERTF.[application_id]=[tbl_application_details].[application_id]
WHERE
    (@pWFTasks IS NULL OR 
     [tbl_worflowprocess].[currenttid] IN (SELECT item 
                                           FROM [dbo].[fnSplit](@pWFTasks,',')))

Any way to improve my query?

Show the query and the whole execution plan. Not just a single tooltip. — Martin Smith, Jul 10 '16 at 14:42
Read http://stackoverflow.com/help/how-to-ask. Post your query as a text inside the question, show full execution plan and clearly state your question. — Nikem, Jul 10 '16 at 14:52
I would be looking at creating some temp tables upfront. Especially for those inner most sub selects. — Jim Hewitt, Jul 10 '16 at 16:54

score 0 · Answer 1 · answered Jul 10 '16 at 18:30

try to create indexes on tables based on the query - use suggested performance improvements if exists and do not interfere with the rest of your DB.
1. if you have table scan in query execution plan, while index already exists on a table on that field - try to change the index to include the columns you select.
2. if you can - avoid using UDF's in case of many results returned by the query
3. if you can pre-calculate - do that using table variables or CTEs even: for example (if it returns more then 1 value) this can be stored in a table variable: SELECT [user_id] FROM [tbl_user] WHERE [establishment_id]=@pCurrentEstablishmentId)
4. queries, such as - "select max(Id) from dbo.tbl_application_comments" - can be improved by using simple variable before the query
5. use SNAPSHOT or READ UNCOMMITED or at least (nolock)
6. make sure statistics on tables are updated!
7. check you are using left join correctly (vs inner join which is faster)
8. limit the number of rows for each join as much as possible - use where statements to cut the data

more can be advise, query optimization is an interesting field

Clustered and non-clustered index seeking increase execution time in stored procedure

1 Answers1