2

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;
  • 2
    What is TPT, for us naive folks? – ypercubeᵀᴹ Feb 24 '18 at 19:47
  • 2
    Why is the GROUP BY needed? – ypercubeᵀᴹ Feb 24 '18 at 20:02
  • 2
    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. – James Legan Feb 24 '18 at 20:06
  • 1
    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 ;) – ypercubeᵀᴹ Feb 24 '18 at 20:11
6

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.

  • 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. – James Legan Feb 25 '18 at 14:04
0

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;
  • 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 Feb 26 '18 at 18:14
  • 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 Feb 26 '18 at 18:20
  • 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 Feb 26 '18 at 18:22
  • 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" /> – James Legan Feb 28 '18 at 1:58
  • @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 . – KumarHarsh Feb 28 '18 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.