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;

• ypercubeᵀᴹ: What is TPT, for us naive folks?
• ypercubeᵀᴹ: Why is the GROUP BY needed?
• James Legan: It is a model for inheritance in relational databases. Table-per-Hierarchy (TPH) and Table-per-Type (TPT) are the most common. TPT aligns most closely with OOP (object oriented programming) model. For example, you have a base Animal table and then you have concrete, specific animal tables (i.e. Dog, Cat). Each of the concrete animal tables have a unique, computed column that defines their type (i.e. AnimalTypeId 1 is Dog, 2 is cat, etc.). As the relationships build out, you have segmented, scoped tables instead of TPH where you will generally find wider tables with a lot of nullable columns.
• ypercubeᵀᴹ: Sorry, I know what table per type is. The "The database is strongly TPT" confused me. Anyway, if it's irrelevant to the issue, let it be ;)

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.

• James Legan: I had missed the cast, thank you. The data in that column is a strict, de-hyphenated GUID but when the value was read out of the incoming JSON query, it was being read out as an NVARCHAR(200). Looks like a copy and paste error. Reading it out as a varchar(32) eliminates the conversion and now plays no role in the showplan from a performance perspective. As for the other suggestion, it makes complete sense and has applicability to another sproc and view combination, very similar to this one which uses a view that is 80 rows wide which uses quite a few TPT tables throughout.

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;

• James Legan: Thank you for the critique @KumarHarsh. On the convert implicits, are they caused by the creation of the dynamic SQL statements themselves? I went through the sproc and what I found from a casting perspective, while many could be reduced in size and converted to varchar to avoid the spare required for the extended character sets, they all seemed to be necessary for generation of the dynamic select statements. I am looking into the Group By right now.
• James Legan: The removal of the GROUP BY from both the count and content queries had zero impact on performance. I can post another showplan if that would help. I also added SET NOCOUNT ON. On the IF/ELSE IF, those are not mutually exclusive query params, both company (that the user belongs to), a user or both can be specified as users can cross tenants. That is why the logic is setup that way.
• James Legan: On the LEFT and OUTER, it is unfortunately necessary. The starting point of this query is the invitation of the user. Not all users are registered but all users, registered and invited need to be searchable. In the case of the unregistered user, a record is still returned. INNER and CROSS would violate that boundary, but I agree, it would be ideal.
• James Legan: I believe you are onto the answer regarding the use of ROW_NUMBER and partitioning over the root of the data, namely the invitation records. As a test, I look the main dynamic SQL statement from the sproc and executed it in SSMS. I then rewrote the same query, partitioning over the PK of the invitation table and the execution time was 75% faster. What I noticed however is that the memory grant is excessive when I do so. See below: <MemoryGrantInfo SerialRequiredMemory="3216" SerialDesiredMemory="7143480" RequiredMemory="3216" GrantedMemory="4356368" MaxUsedMemory="32768" />
• KumarHarsh: @JamesLegan,I said that first try to eliminate duplicate record by writing proper query i.e. improving cardianility estimate.If it is not possible then try row_number.I wrote few other thing too. Writing query in proper way may minimize memory grant too.Lastly you have to decide speed vs cost .