Dynamic SQL Query with Paging, Performance and Optimization

Question

I have an interesting problem in front of me. There is a database with ~1 million user accounts with an anticipated growth of 1-2 million per year. The database is strongly TPT but this particular query and the tables involved do not touch any of the TPT content.

The current design of the sproc and view takes ~15 seconds to execute (x2) when specifying a second data point (i.e. email address and last name, company, etc). The database is an SQL Azure P11, though it is not a DTU bound query and upgrading to the highest available offering (P15) has no impact on the results.

Below is the sproc, view and execution plans. All index were rebuilt or reorg'd within the last 24 hours and all statistics updated. In looking at the data, for example, the concept of historical email addresses (1..N), is currently utilizing a CROSS APPLY to grab the latest, which prevents an indexed view and could be solved by a simple concatenation of historical email addresses and persist them in a single column.

A lot of the database utilizes JSON in nvarchar(4000-max) columns, all of which have a computed column exposing the value and enabling indexing. The paradigm must support paging and I am looking for feedback/advice on how to optimize it.

Changing the table structure is not a viable option at this point, although I can see a path forward with a little bit of manipulation. Does anyone have any thoughts on where I should look first? I have tested both recompiling and optimizing for unknown to see if there was any impact and if there was, it was negligible.

Note: Some business logic (proprietary column names have been removed or modified, the sproc and view cannot be executed as is but are functionally the same as the source.

Execution plan and the View

Sproc

CREATE PROCEDURE [dbo].[spGetUserDetailsDynamic] @JsonFilter NVARCHAR(MAX)
AS /* Page number*/
DECLARE @Page AS INT = JSON_VALUE(@JsonFilter, '$.requestPaging.page');
/* Number of records on the page*/
DECLARE @Size AS INT = JSON_VALUE(@JsonFilter, '$.requestPaging.size');

IF (@Page = -1)
    SET @Page = 1;

IF (@Size = -1)
    SET @Size = 32767;

/* Sort direction ASC or DESC*/
DECLARE @SortDirection AS VARCHAR(10) = JSON_VALUE(@JsonFilter, '$.requestSorting.direction');
/* Order By Column */
DECLARE @SortColumn AS VARCHAR(200) = JSON_QUERY(@JsonFilter, '$.requestSorting.keys');

SET @SortColumn = REPLACE(@SortColumn, '"', '');
SET @SortColumn = REPLACE(@SortColumn, '[', '');
SET @SortColumn = REPLACE(@SortColumn, ']', '');

/* Filters*/
DECLARE @RequestorApplicationIdFilterValue AS TINYINT
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.applicationIdValue');

DECLARE @CompanyAssignedKeyFilterValue AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.companyAssignedKeyValue');
DECLARE @CompanyAssignedKeyFilterOperation AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.companyAssignedKeyOperation');


DECLARE @EmailFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.emailValue');
DECLARE @EmailFilterOperation AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.emailOperation');


DECLARE @LastNameFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.lastNameValue');
DECLARE @LastNameFilterOperation AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.lastNameOperation');


DECLARE @FirstNameFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.firstNameValue');
DECLARE @FirstNameFilterOperation AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.firstNameOperation');


DECLARE @PhoneNumberFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.phoneNumberValue');
DECLARE @PhoneNumberFilterOperation AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.phoneNumberOperation');

DECLARE @StreetAddressFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.streetAddressValue');
DECLARE @StreetAddressFilterOperation AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.streetAddressOperation');

DECLARE @CityFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.cityValue');
DECLARE @CityFilterOperation AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.cityOperation');

DECLARE @RegionFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.regionValue');
DECLARE @RegionFilterOperation AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.regionOperation');

DECLARE @PostalCodeFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.postalCodeValue');
DECLARE @PostalCodeFilterOperation AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.postalCodeOperation');

DECLARE @CountryFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.countryValue');


DECLARE @HasCompletedRegistrationFilterValue AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.completedRegistrationValue');
DECLARE @HasCompletedRegistrationBitValue AS BIT = NULL;

IF (@HasCompletedRegistrationFilterValue IS NOT NULL)
BEGIN
    IF (@HasCompletedRegistrationFilterValue = 'true')
        SET @HasCompletedRegistrationBitValue = 1;
    ELSE
        SET @HasCompletedRegistrationBitValue = 0;
END;


DECLARE @HasApplicationAccountsFilterValue AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.haveApplicationAccountsValue');

DECLARE @HasApplicationAccountsBitValue AS BIT = NULL;

IF (@HasApplicationAccountsFilterValue IS NOT NULL)
BEGIN
    IF (@HasApplicationAccountsFilterValue = 'true')
        SET @HasApplicationAccountsBitValue = 1;
    ELSE
        SET @HasApplicationAccountsBitValue = 0;
END;

DECLARE @HasProcessorAccountsFilterValue AS NVARCHAR(200)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.haveProcessorAccountsValue');

DECLARE @HasProcessorAccountsBitValue AS BIT = NULL;

IF (@HasProcessorAccountsFilterValue IS NOT NULL)
BEGIN
    IF (@HasProcessorAccountsFilterValue = 'true')
        SET @HasProcessorAccountsBitValue = 1;
    ELSE
        SET @HasProcessorAccountsBitValue = 0;
END;


DECLARE @TargetApplicationIdFilterValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.userTypeValue');


DECLARE @CompanyPublicIdValue AS NVARCHAR(200) = JSON_VALUE(@JsonFilter, '$.requestFiltering.companyPublicIdValue');
DECLARE @CompanyUserIdValue INT = NULL;

IF (@CompanyPublicIdValue IS NOT NULL)
BEGIN
    SET @CompanyUserIdValue =
    (
        SELECT UserId
        FROM Application.Users
        WHERE UserPublicId = @CompanyPublicIdValue
    );
END;


DECLARE @AccountPublicIdFilterValue AS VARCHAR(32)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.accountPublicIdValue');

DECLARE @AccountIdFilterValue AS INT = JSON_VALUE(@JsonFilter, '$.requestFiltering.accountIdValue');



-- User Public ID
DECLARE @UserPublicIdFilterValue AS VARCHAR(32) = JSON_VALUE(@JsonFilter, '$.requestFiltering.userPublicIdValue');
DECLARE @UserIdFilterValue AS INT = JSON_VALUE(@JsonFilter, '$.requestFiltering.userIdValue');
IF (@UserPublicIdFilterValue IS NOT NULL AND @UserIdFilterValue IS NULL)
    SET @UserIdFilterValue =
(
    SELECT UserId
    FROM Application.Users
    WHERE UserPublicId = @UserPublicIdFilterValue
)   ;

DECLARE @UserRegistrationProgressStateFilterValue AS VARCHAR(32)
    = JSON_VALUE(@JsonFilter, '$.requestFiltering.userRegistrationProgressStateValue');
DECLARE @UserRegistrationProgressStateValue AS NVARCHAR(200) = NULL;

IF (@UserRegistrationProgressStateFilterValue IS NOT NULL)
BEGIN
    IF (@UserRegistrationProgressStateFilterValue = '4')
        SET @UserRegistrationProgressStateValue = '1,2';
    ELSE
        SET @UserRegistrationProgressStateValue = @UserRegistrationProgressStateFilterValue;
END;
    
DECLARE @TotalRecordCount INT;

DECLARE @WHERE AS NVARCHAR(MAX) = '';
DECLARE @GROUPBY AS NVARCHAR(MAX) = '';


DECLARE @SQLViewCount AS NVARCHAR(MAX)
    = '
    SELECT  @_TotalRecordCount = SUM(S.Total)
          FROM    (
                  SELECT    1 AS Total
                  FROM      Application.vwUserDetails T ';

DECLARE @SQL AS NVARCHAR(MAX)
    = '
    SELECT [Application_UserInvitations_ApplicationId]
      ,[Application_UserInvitations_EmailAddress]
      ,[Application_UserInvitations_InvitationStatusTypeId]
      ,[Application_UserInvitations_InvitationKey]
      ,[Application_UserInvitations_CompanyUserId]
      ,[Application_UserInvitations_Created]
      ,[Application_UserInvitations_Expires]
      ,[Application_Users_UserPublicId]
      ,[Application_Users_UserId]
      ,[Application_Users_UserCompanyAssignedKey]
      ,[Application_Users_FirstName]
      ,[Application_Users_LastName]
      ,[Application_Users_Created]
      ,[Application_Users_CompanyUserId]
      ,[Application_Users_ApplicationId]
      ,[Application_UserEmailAddresses_VerifiedEmailAddress]
      ,[Application_UserEmailAddresses_UnverifiedEmailAddress]
      ,[Application_UserInvitationRegistrationInformation_UserCompanyAssignedKey]
      ,[Application_UserInvitationRegistrationInformation_FirstName]
      ,[Application_UserInvitationRegistrationInformation_LastName]
      ,[Application_Companies_Name]
      ,[Application_Companies_UserTitleSubscriptionKey]
      ,[Application_Companies_CompanyUserId]
      ,[Application_Companies_CompanyId]
      ,[Application_Users_EvaluatedEmailAddress]
      ,[Application_Users_EvaluatedFirstName]
      ,[Application_Users_EvaluatedLastName]
      ,[Application_Users_EvaluatedUserCompanyAssignedKey]
      ,[Application_UserProfiles_PrimaryPhoneNumber]
      ,[Application_UserProfiles_MobilePhoneNumber]
      ,[UserHasApplicationAccount]
      ,[UserHasFinancialProcessorAccount]
      ,[Application_RegisterDate]
      ,[Application_Registration_Progress]
      ,[Application_Evaluated_User_Registration_Progress_Step]
      ,[Application_EvaluatedPhoneNumberCountry]
  FROM [Application].[vwUserDetails] T ';


DECLARE @SQLOrderBy AS NVARCHAR(200)
    = '
        
    ORDER BY 
        ' + ' ' + @SortColumn + ' ' + @SortDirection
      + '
    OFFSET (@_Page-1)*@_Size ROWS
        FETCH NEXT @_Size ROWS ONLY
    ';



/* Based off of requesting application, build the where clause, first with application specific filters, followed by general.*/
IF (
       @RequestorApplicationIdFilterValue IS NULL
       OR @RequestorApplicationIdFilterValue NOT IN ( 1, 2, 3 )
   )
BEGIN
    THROW 60000, 'ApplicationIdFilterValue is null or out of range', 1;
END;



IF (@CountryFilterValue IS NOT NULL)
BEGIN
    SET @WHERE
        = @WHERE + 'AND Application_UserAccountAddressCountryCode IN (''' + CAST(@CountryFilterValue AS VARCHAR(100))
          + ''') ';
END;

IF (@TargetApplicationIdFilterValue IS NOT NULL)
BEGIN
    SET @WHERE
        = @WHERE + 'AND Application_UserInvitations_ApplicationId IN ('
          + CAST(@TargetApplicationIdFilterValue AS VARCHAR(100)) + ') ';
END;

IF (@CompanyUserIdValue IS NOT NULL)
BEGIN
    SET @WHERE
        = @WHERE + 'AND Application_UserInvitations_CompanyUserId IN (' + CAST(@CompanyUserIdValue AS VARCHAR(100)) + ') ';
END;

IF (@HasProcessorAccountsBitValue IS NOT NULL)
BEGIN
    SET @WHERE
        = @WHERE + 'AND UserHasFinancialProcessorAccount = ' + CAST(@HasProcessorAccountsBitValue AS VARCHAR(100))
          + ' ';
END;

IF (@HasApplicationAccountsBitValue IS NOT NULL)
BEGIN
    SET @WHERE = @WHERE + 'AND UserHasApplicationAccount = ' + CAST(@HasApplicationAccountsBitValue AS VARCHAR(100)) + ' ';
END;

IF (@UserRegistrationProgressStateValue IS NOT NULL)
BEGIN
    SET @WHERE = @WHERE + 'AND Application_Registration_Progress IN (' + @UserRegistrationProgressStateValue + ') ';
END;

/* Fuzzy Text Search */
IF (@EmailFilterValue IS NOT NULL AND @EmailFilterOperation IS NOT NULL)
BEGIN
    -- IsEqualTo
    IF (@EmailFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedEmailAddress = ''' + @EmailFilterValue + ''')';
    -- StartsWith
    ELSE IF (@EmailFilterOperation = 2)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedEmailAddress LIKE ''' + @EmailFilterValue + '%'')';
    -- Contains
    ELSE IF (@EmailFilterOperation = 3)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedEmailAddress LIKE ''%' + @EmailFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@EmailFilterOperation = 5)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedEmailAddress LIKE ''%' + @EmailFilterValue + ''')';
END;

IF (
       @CompanyAssignedKeyFilterValue IS NOT NULL
       AND @CompanyAssignedKeyFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@CompanyAssignedKeyFilterOperation = 0)
        SET @WHERE
            = @WHERE + 'AND (Application_Users_EvaluatedUserCompanyAssignedKey = ''' + @CompanyAssignedKeyFilterValue
              + ''')';
    -- StartsWith
    ELSE IF (@CompanyAssignedKeyFilterOperation = 2)
        SET @WHERE
            = @WHERE + 'AND (Application_Users_EvaluatedUserCompanyAssignedKey LIKE ''' + @CompanyAssignedKeyFilterValue
              + '%'')';
    -- Contains
    ELSE IF (@CompanyAssignedKeyFilterOperation = 3)
        SET @WHERE
            = @WHERE + 'AND (Application_Users_EvaluatedUserCompanyAssignedKey LIKE ''%' + @CompanyAssignedKeyFilterValue
              + '%'')';
    -- EndsWith
    ELSE IF (@CompanyAssignedKeyFilterOperation = 5)
        SET @WHERE
            = @WHERE + 'AND (Application_Users_EvaluatedUserCompanyAssignedKey LIKE ''%' + @CompanyAssignedKeyFilterValue
              + ''')';
END;

IF (
       @FirstNameFilterValue IS NOT NULL
       AND @FirstNameFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@FirstNameFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedFirstName = ''' + @FirstNameFilterValue + ''')';
    -- StartsWith
    ELSE IF (@FirstNameFilterOperation = 2)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedFirstName LIKE ''' + @FirstNameFilterValue + '%'')';
    -- Contains
    ELSE IF (@FirstNameFilterOperation = 3)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedFirstName LIKE ''%' + @FirstNameFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@FirstNameFilterOperation = 5)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedFirstName LIKE ''%' + @FirstNameFilterValue + ''')';
END;

IF (
       @LastNameFilterValue IS NOT NULL
       AND @LastNameFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@LastNameFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedLastName = ''' + @LastNameFilterValue + ''')';
    -- StartsWith
    ELSE IF (@LastNameFilterOperation = 2)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedLastName LIKE ''' + @LastNameFilterValue + '%'')';
    -- Contains
    ELSE IF (@LastNameFilterOperation = 3)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedLastName LIKE ''%' + @LastNameFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@LastNameFilterOperation = 5)
        SET @WHERE = @WHERE + 'AND (Application_Users_EvaluatedLastName LIKE ''%' + @LastNameFilterValue + ''')';
END;

IF (@CityFilterValue IS NOT NULL AND @CityFilterOperation IS NOT NULL)
BEGIN
    -- IsEqualTo
    IF (@CityFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_City = ''' + @CityFilterValue + ''')';
    -- StartsWith
    ELSE IF (@CityFilterOperation = 2)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_City LIKE ''' + @CityFilterValue + '%'')';
    -- Contains
    ELSE IF (@CityFilterOperation = 3)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_City LIKE ''%' + @CityFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@CityFilterOperation = 5)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_City LIKE ''%' + @CityFilterValue + ''')';
END;

IF (@RegionFilterValue IS NOT NULL AND @RegionFilterOperation IS NOT NULL)
BEGIN
    -- IsEqualTo
    IF (@RegionFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_Region = ''' + @RegionFilterValue + ''')';
    -- StartsWith
    ELSE IF (@RegionFilterOperation = 2)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_Region LIKE ''' + @RegionFilterValue + '%'')';
    -- Contains
    ELSE IF (@RegionFilterOperation = 3)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_Region LIKE ''%' + @RegionFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@RegionFilterOperation = 5)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_Region LIKE ''%' + @RegionFilterValue + ''')';
END;

IF (
       @PostalCodeFilterValue IS NOT NULL
       AND @PostalCodeFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@PostalCodeFilterOperation = 0)
        SET @WHERE = @WHERE + 'AND (Application_UserAccountAddresses_PostalCode = ''' + @PostalCodeFilterValue + ''')';
    -- StartsWith
    ELSE IF (@PostalCodeFilterOperation = 2)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_PostalCode LIKE ''' + @PostalCodeFilterValue + '%'')';
    -- Contains
    ELSE IF (@PostalCodeFilterOperation = 3)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_PostalCode LIKE ''%' + @PostalCodeFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@PostalCodeFilterOperation = 5)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_PostalCode LIKE ''%' + @PostalCodeFilterValue + ''')';
END;

IF (
       @StreetAddressFilterValue IS NOT NULL
       AND @StreetAddressFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@StreetAddressFilterOperation = 0)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_StreetAddress1 = ''' + @StreetAddressFilterValue
              + ''' OR Application_UserAccountAddresses_StreetAddress2 = ''' + @StreetAddressFilterValue
              + ''' OR Application_UserAccountAddresses_StreetAddress3 = ''' + @StreetAddressFilterValue + ''')';
    -- StartsWith
    ELSE IF (@StreetAddressFilterOperation = 2)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_StreetAddress1 LIKE ''' + @StreetAddressFilterValue
              + '%'' OR Application_UserAccountAddresses_StreetAddress2 LIKE ''' + @StreetAddressFilterValue
              + '%'' OR Application_UserAccountAddresses_StreetAddress3 LIKE ''' + @StreetAddressFilterValue + '%'')';
    -- Contains
    ELSE IF (@StreetAddressFilterOperation = 3)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_StreetAddress1 LIKE ''%' + @StreetAddressFilterValue
              + '%'' OR Application_UserAccountAddresses_StreetAddress2 LIKE ''%' + @StreetAddressFilterValue
              + '%'' OR Application_UserAccountAddresses_StreetAddress3 LIKE ''%' + @StreetAddressFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@StreetAddressFilterOperation = 5)
        SET @WHERE
            = @WHERE + 'AND (Application_UserAccountAddresses_StreetAddress1 LIKE ''%' + @StreetAddressFilterValue
              + ''' OR Application_UserAccountAddresses_StreetAddress2 LIKE ''%' + @StreetAddressFilterValue
              + ''' OR Application_UserAccountAddresses_StreetAddress3 LIKE ''%' + @StreetAddressFilterValue + ''')';
END;

IF (
       @PhoneNumberFilterValue IS NOT NULL
       AND @PhoneNumberFilterOperation IS NOT NULL
   )
BEGIN
    -- IsEqualTo
    IF (@PhoneNumberFilterOperation = 0)
        SET @WHERE
            = @WHERE + 'AND (Application_UserProfiles_Profile_vPrimaryTelephone_Numeric = ''' + @PhoneNumberFilterValue
              + ''' OR Application_UserProfiles_Profile_vMobileTelephone_Numeric = ''' + @PhoneNumberFilterValue + ''')';
    -- StartsWith
    ELSE IF (@PhoneNumberFilterOperation = 2)
        SET @WHERE
            = @WHERE + 'AND (Application_UserProfiles_Profile_vPrimaryTelephone_Numeric LIKE ''' + @PhoneNumberFilterValue
              + ''' OR Application_UserProfiles_Profile_vMobileTelephone_Numeric LIKE ''' + @PhoneNumberFilterValue
              + '%'')';
    -- Contains
    ELSE IF (@PhoneNumberFilterOperation = 3)
        SET @WHERE
            = @WHERE + 'AND (Application_UserProfiles_Profile_vPrimaryTelephone_Numeric LIKE ''%'
              + @PhoneNumberFilterValue + '%'' OR Application_UserProfiles_Profile_vMobileTelephone_Numeric LIKE ''%'
              + @PhoneNumberFilterValue + '%'')';
    -- EndsWith
    ELSE IF (@PhoneNumberFilterOperation = 5)
        SET @WHERE
            = @WHERE + 'AND (Application_UserProfiles_Profile_vPrimaryTelephone_Numeric LIKE ''%'
              + @PhoneNumberFilterValue + ''' OR Application_UserProfiles_Profile_vMobileTelephone_Numeric LIKE ''%'
              + @PhoneNumberFilterValue + ''')';
END;


SET @GROUPBY
    = '[Application_UserInvitations_ApplicationId]
      ,[Application_UserInvitations_EmailAddress]
      ,[Application_UserInvitations_InvitationStatusTypeId]
      ,[Application_UserInvitations_InvitationKey]
      ,[Application_UserInvitations_Created]
      ,[Application_UserInvitations_Expires]
      ,[Application_UserInvitations_CompanyUserId]
      ,[Application_Users_UserPublicId]
      ,[Application_Users_UserId]
      ,[Application_Users_UserCompanyAssignedKey]
      ,[Application_Users_FirstName]
      ,[Application_Users_LastName]
      ,[Application_Users_Created]
      ,[Application_Users_CompanyUserId]
      ,[Application_Users_ApplicationId]
      ,[Application_UserEmailAddresses_VerifiedEmailAddress]
      ,[Application_UserEmailAddresses_UnverifiedEmailAddress]
      ,[Application_UserInvitationRegistrationInformation_UserCompanyAssignedKey]
      ,[Application_UserInvitationRegistrationInformation_FirstName]
      ,[Application_UserInvitationRegistrationInformation_LastName]
      ,[Application_Companies_Name]
      ,[Application_Companies_UserTitleSubscriptionKey]
      ,[Application_Companies_CompanyUserId]
      ,[Application_Companies_CompanyId]
      ,[Application_Users_EvaluatedEmailAddress]
      ,[Application_Users_EvaluatedFirstName]
      ,[Application_Users_EvaluatedLastName]
      ,[Application_Users_EvaluatedUserCompanyAssignedKey]
      ,[Application_UserProfiles_PrimaryPhoneNumber]
      ,[Application_UserProfiles_MobilePhoneNumber]
      ,[UserHasApplicationAccount]
      ,[UserHasFinancialProcessorAccount]
      ,[Application_RegisterDate]
      ,[Application_Registration_Progress]
      ,[Application_Evaluated_User_Registration_Progress_Step]
      ,[Application_EvaluatedPhoneNumberCountry]';

/* Always hide cloaked*/
SET @WHERE = @WHERE + 'AND Application_UserInvitations_InvitationStatusTypeId != 4 ';

/* Build SQL and dynamic WHERE clause */
IF LEN(@WHERE) > 0
BEGIN
    -- Where clause
    SET @SQLViewCount = @SQLViewCount + ' WHERE ' + RIGHT(@WHERE, LEN(@WHERE) - 3);

    -- Group by
    SET @SQLViewCount = @SQLViewCount + ' GROUP BY ' + RIGHT(@GROUPBY, LEN(@GROUPBY) - 0);
    SET @SQLViewCount = @SQLViewCount + ') S';


    -- Main SELECT dynamic SQL
    SET @SQL = @SQL + ' WHERE ' + RIGHT(@WHERE, LEN(@WHERE) - 3);


    SET @SQL = @SQL + ' GROUP BY ' + RIGHT(@GROUPBY, LEN(@GROUPBY) - 0) + ' ' + @SQLOrderBy;


    -- Execute View Total Record Count 
    EXEC sp_executesql @SQLViewCount,
                       N'@_TotalRecordCount 
                INT OUTPUT',
                       @_TotalRecordCount = @TotalRecordCount OUTPUT;

    -- Summarise Counts, Size and Page (resultset 1)
    SELECT CAST(CEILING((CAST(@TotalRecordCount AS FLOAT) / (CAST(@Size AS FLOAT)))) AS INT) AS TotalPages,
           @TotalRecordCount AS TotalRecordCount,
           CASE
               WHEN @Size = 32767 THEN
                   @TotalRecordCount
               ELSE
                   @Size
           END AS PageSize,
           @Page AS PageNumber;

    -- Execute main SELECT (resultset 2) 
    EXEC sp_executesql @SQL,
                       N'@_Page
                INT,
             @_Size 
                INT,
             @_SortColumn
                VARCHAR(40)',
                       @_Page = @Page,
                       @_Size = @Size,
                       @_SortColumn = @SortColumn;

END;

Answer 1

Two easy improvements:

1. Fix this query by casting @CompanyPublicIdValue to the same type as UserPublicId. @CompanyPublicIdValue is an nvarchar(32), which has a higher data type precedence than the column type. So the comparison requires a conversion on every row.

   It's expensive and shouldn't be.

   SET @CompanyUserIdValue =
       (
           SELECT UserId
           FROM Application.Users
           WHERE UserPublicId = @CompanyPublicIdValue
       )
   

2. Instead of counting the rows in one query and returning them in the next, fetch the key values into a temp table/table variable in the first query, and join that in the second query.

Then work on improving the query that identifies the keys to be returned. Perhaps that query can hit the base tables directly, or an indexed view. Then the final query joins your existing view to the temp table of key values.

Answer 2

There are 18 CONVERT_IMPLICIT in your Execution plan.Find each and fix them one by one.

NVARCHAR and VARCHAR are very frequently and interchangeable use.I think you should use VARCHAR and limit the size as far as possible.

You are missing **SET NOCOUNT ON** in your proc.

Your proc is still fast because of paging.

Main issue it appear is Group By.It appear that you are using Group By to eliminate duplicate records.

So either your query is wrong,so find which join is causing many records.

Or use ROW_NUMBER (window function ) to eliminate duplicate records.

If Group BY is not required remove it even if there is no improvement in performance you will see the improvement later on.

In fact you have use only LEFT JOIN and OUTER APPLY ,see that if you can convert it into INNER and CROSS APPLY respectively.

registered and invited need

This may be your requirement, but you need to thoroughly examine each and every join and eliminate Left Join.

I believe that you are getting more record than require so you are using Group By. If this is so this is the reason behind poor performance.

I will suggest comment all join except 2 in views,remove Group By in your proc and start testing and debugging,and see that you get correct number of rows and slowly remove one join at a time.

Say for example,in this OUTER APPLY,

OUTER APPLY
    (
        SELECT TOP 1 RAAA.Application_AccountAddresses_AddressDetails_vCountry
        FROM Application.Accounts RACA
            JOIN Application.AccountAddresses RAAA
                ON RAAA.AccountId = RACA.AccountId
        WHERE RACA.UserId = U.UserId
              AND RAAA.AddressTypeId = 1
        ORDER BY RAAA.AccountAddressId DESC
    ) AS ResidentialAddressCountry

Here both table are INNER JOIN but same tables in main query are LEFT JOIN.

Is it correct ?

Is OUTER APPLY of ResidentialAddressCountry and BusinessAddressCountry necessary ?

Instead write ,

CASE 
        WHEN AddressTypeId = 1
            AND AA.Application_AccountAddresses_AddressDetails_vCountry IS NOT NULL
            THEN ResidentialAddressCountry.Application_AccountAddresses_AddressDetails_vCountry
        WHEN AddressTypeId = 3
            AND AA.BusinessAddressCountry.Application_AccountAddresses_AddressDetails_vCountry IS NOT NULL
            THEN BusinessAddressCountry.Application_AccountAddresses_AddressDetails_vCountry
        ELSE NULL
        END Application_EvaluatedPhoneNumberCountry

and comment the both outer apply,notice the table alias for better understanding, and sorry if I am wrong.

Application.fnRemoveNonNumericCharacters

Suppose I want to use same view but I do not want to remove non numeric or I do not want to concat,then should I create new view ?

Let the user decide about these small issue.Secondly UDF are notoriously bad perform-ant .

So remove UDF from view.DO your "removing non numeric character work" inline in proc

you can SAFELY write this at the top after fetching value.

IF (
       @RequestorApplicationIdFilterValue IS NULL
       OR @RequestorApplicationIdFilterValue NOT IN ( 1, 2, 3 )
   )
BEGIN
    THROW 60000, 'ApplicationIdFilterValue is null or out of range', 1;
END;