Thanks for the suggestion but that Index is already on the Generic Attribute table. I also worry that including Value on the NonClustered index will create a larger performance hit since the field is an NVARCHAR(MAX) and we have close to 4,000,000 Generic Attributes.
I went ahead and created a StoredProcedure and updated the GetAllCustomers method in the CustomerService class.
Searches that were taking 30 seconds to complete now take less than a second. This is a good interim fix but I still believe the First Name, Last Name, and Company fields should be added to the Customer table. Especially since you're retrieving this information after the initial search is made which requires at least 1 additional query for every customer record returned.
This is the 4.2 code but I also have 4.0 if anyone want's it.
public IPagedList<Customer> GetAllCustomers(DateTime? createdFromUtc = null, DateTime? createdToUtc = null,
int affiliateId = 0, int vendorId = 0, int[] customerRoleIds = null,
string email = null, string username = null, string firstName = null, string lastName = null,
int dayOfBirth = 0, int monthOfBirth = 0,
string company = null, string phone = null, string zipPostalCode = null, string ipAddress = null,
int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false)
{
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
pageSize = int.MaxValue - 1;
//pass customer role identifiers as comma-delimited string
var commaSeparatedCustomerRoleIds = string.Join(",", customerRoleIds);
//Use our custom stored procedure instead
DbParameter pCreatedFromUtc = null;
if (createdFromUtc.HasValue)
pCreatedFromUtc = _dataProvider.GetDateTimeParameter("CreatedFromUtc", createdFromUtc.Value);
else
pCreatedFromUtc = _dataProvider.GetDateTimeParameter("CreatedFromUtc", null);
DbParameter pCreatedToUtc = null;
if (createdToUtc.HasValue)
pCreatedToUtc = _dataProvider.GetDateTimeParameter("CreatedToUtc", createdToUtc.Value);
else
pCreatedToUtc = _dataProvider.GetDateTimeParameter("CreatedToUtc", null);
var pAffiliateId = _dataProvider.GetInt32Parameter("AffiliateId", affiliateId);
var pVendorId = _dataProvider.GetInt32Parameter("VendorId", vendorId);
var pCustomerRoleIds = _dataProvider.GetStringParameter("CustomerRoleIds", commaSeparatedCustomerRoleIds);
var pEmail = _dataProvider.GetStringParameter("Email", email);
var pUsername = _dataProvider.GetStringParameter("Username", username);
var pFirstName = _dataProvider.GetStringParameter("FirstName", firstName);
var pLastName = _dataProvider.GetStringParameter("LastName", lastName);
var pDayOfBirth = _dataProvider.GetInt32Parameter("DayOfBirth", dayOfBirth);
var pMonthOfBirth = _dataProvider.GetInt32Parameter("MonthOfBirth", monthOfBirth);
var pCompany = _dataProvider.GetStringParameter("Company", company);
var pPhone = _dataProvider.GetStringParameter("Phone", phone);
var pZipPostalCode = _dataProvider.GetStringParameter("ZipPostalCode", zipPostalCode);
var pIpAddress = _dataProvider.GetStringParameter("IpAddress", CommonHelper.IsValidIpAddress(ipAddress) ? ipAddress : null);
var pPageIndex = _dataProvider.GetInt32Parameter("PageIndex", pageIndex);
var pPageSize = _dataProvider.GetInt32Parameter("PageSize", pageSize);
var pTotalRecords = _dataProvider.GetOutputInt32Parameter("TotalRecords");
var customers = _dbContext.EntityFromSql<Customer>(
"GetAllCustomers",
pCreatedFromUtc,
pCreatedToUtc,
pAffiliateId,
pVendorId,
pCustomerRoleIds,
pEmail,
pUsername,
pFirstName,
pLastName,
pDayOfBirth,
pMonthOfBirth,
pCompany,
pPhone,
pZipPostalCode,
pIpAddress,
pPageIndex,
pPageSize,
pTotalRecords).ToList();
var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
return new PagedList<Customer>(customers, pageIndex, pageSize, totalRecords);
}
And the stored Procedure
-- =============================================
-- Author: oumiller21
-- Create date: 2019-08-29
-- Description: Search for Customers (Performance improvement over LINQ query)
-- =============================================
/*
DECLARE @CreatedFromUtc DateTime = null,
@CreatedToUtc DateTime = null,
@AffiliateId int = 0,
@VendorId int = 0,
@CustomerRoleIds nvarchar(1000) = null,
@Email nvarchar(1000) = null,
@UserName nvarchar(1000) = null,
@FirstName nvarchar(MAX) = null,
@LastName nvarchar(MAX) = null,
@DayOfBirth int = 0,
@MonthOfBirth int = 0,
@Company nvarchar(MAX) = null,
@Phone nvarchar(MAX) = null,
@ZipPostalCode nvarchar(MAX) = null,
@IpAddress nvarchar(MAX) = null,
@PageIndex int = 0,
@PageSize int = 2147483644,
@TotalRecords int = null
SELECT
--@CreatedFromUtc = CONVERT(datetime,'2019-01-01')
--, @CreatedToUtc = CONVERT(datetime,'2020-01-01')
--, @AffiliateId = 1
--, @VendorId = 1
@CustomerRoleIds = '3'
-- @Email = 'email.com'
--, @UserName = 'myusername'
--, @FirstName = 'First'
--, @LastName = 'Last'
--, @DayOfBirth = 28
--, @MonthOfBirth = 6
--, @Company = 'Max'
--, @Phone = '555-444-3333'
--, @ZipPostalCode = '47012'
--, @IpAddress = '40.77.167.113'
, @PageIndex = 0
, @PageSize = 1
EXEC GetAllCustomers @CreatedFromUtc
, @CreatedToUtc
, @AffiliateId
, @VendorId
, @CustomerRoleIds
, @Email
, @UserName
, @FirstName
, @LastName
, @DayOfBirth
, @MonthOfBirth
, @Company
, @Phone
, @ZipPostalCode
, @IpAddress
, @PageIndex
, @PageSize
, @TotalRecords output
Select @TotalRecords
*/
CREATE PROCEDURE [dbo].[GetAllCustomers]
(
@CreatedFromUtc DateTime = null, --Created date from (UTC); null to load all records
@CreatedToUtc DateTime = null, --Created date to (UTC); null to load all records
@AffiliateId int = 0, --Affiliate identifier
@VendorId int = 0, --Vendor identifier
@CustomerRoleIds nvarchar(1000) = null, --A list of customer role identifiers to filter by (at least one match); pass null or empty list in order to load all customers;
@Email nvarchar(1000) = null, --Email; null to load all customers
@UserName nvarchar(1000) = null, --Username; null to load all customers
@FirstName nvarchar(MAX) = null, --First name; null to load all customer
@LastName nvarchar(MAX) = null, --Last name; null to load all customer
@DayOfBirth int = 0, --Day of birth; 0 to load all customers
@MonthOfBirth int = 0, --Month of birth; 0 to load all customers
@Company nvarchar(MAX) = null, --Company; null to load all customers
@Phone nvarchar(MAX) = null, --Phone; null to load all customers
@ZipPostalCode nvarchar(MAX) = null, --ZipPostalCode; null to load all customers
@IpAddress nvarchar(MAX) = null, --IP address; null to load all customers
@PageIndex int = 0,
@PageSize int = 2147483644,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
DECLARE @sql nvarchar(max),
@sql_orderby nvarchar(max),
@DayOfBirthPadded nvarchar(2),
@MonthOfBirthPadded nvarchar(2),
@nl char(2) = char(13) + char(10)
--Start the Query
SELECT @sql = '
SELECT c.Id
, TotalCount = COUNT(1) OVER()
FROM Customer c (NOLOCK)' + @nl
IF NULLIF(@CustomerRoleIds, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN Customer_CustomerRole_Mapping ccrm (NOLOCK) ON c.Id = ccrm.Customer_Id
AND ccrm.CustomerRole_Id IN (' + @CustomerRoleIds + ')' + @nl
END
IF NULLIF(@FirstName, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute fn (NOLOCK) ON c.Id = fn.EntityId
AND fn.KeyGroup = ''Customer''
AND fn.[Key] = ''FirstName''
AND fn.StoreId = 0
AND fn.Value like ''%' + @FirstName + '%''' + @nl
END
IF NULLIF(@LastName, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute ln (NOLOCK) ON c.Id = ln.EntityId
AND ln.KeyGroup = ''Customer''
AND ln.[Key] = ''LastName''
AND ln.StoreId = 0
AND ln.Value like ''%' + @LastName + '%''' + @nl
END
--date of birth is stored as a string into database.
--we also know that date of birth is stored in the following format YYYY-MM-DD (for example, 1983-02-18).
--so let's search it as a string
Select @DayOfBirthPadded = RIGHT('00' + CONVERT(nvarchar, @DayOfBirth), 2)
, @MonthOfBirthPadded = RIGHT('00' + CONVERT(nvarchar, @MonthOfBirth), 2);
IF @DayOfBirth > 0 AND @MonthOfBirth > 0
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute dob (NOLOCK) ON c.Id = dob.EntityId
AND dob.KeyGroup = ''Customer''
AND dob.[Key] = ''DateOfBirth''
AND dob.StoreId = 0
AND SUBSTRING(dob.Value, 5, 5) = ''' + @MonthOfBirthPadded + '-' + @DayOfBirthPadded + '''' + @nl
END
ELSE IF @DayOfBirth > 0
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute dob (NOLOCK) ON c.Id = dob.EntityId
AND dob.KeyGroup = ''Customer''
AND dob.[Key] = ''DateOfBirth''
AND dob.StoreId = 0
AND SUBSTRING(dob.Value, 8, 2) = ''' + @DayOfBirthPadded + '''' + @nl
END
ELSE IF @MonthOfBirth > 0
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute dob (NOLOCK) ON c.Id = dob.EntityId
AND dob.KeyGroup = ''Customer''
AND dob.[Key] = ''DateOfBirth''
AND dob.StoreId = 0
AND dob.Value like ''%-' + @MonthOfBirthPadded + '-%''' + @nl
END
IF NULLIF(@Company, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute cn (NOLOCK) ON c.Id = cn.EntityId
AND cn.KeyGroup = ''Customer''
AND cn.[Key] = ''Company''
AND cn.StoreId = 0
AND cn.Value like ''%' + @Company + '%''' + @nl
END
IF NULLIF(@Phone, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute p (NOLOCK) ON c.Id = p.EntityId
AND p.KeyGroup = ''Customer''
AND p.[Key] = ''Phone''
AND p.StoreId = 0
AND p.Value like ''%' + @Phone + '%''' + @nl
END
IF NULLIF(@ZipPostalCode, '') IS NOT NULL
BEGIN
SELECT @sql += '
INNER JOIN GenericAttribute z (NOLOCK) ON c.Id = z.EntityId
AND z.KeyGroup = ''Customer''
AND z.[Key] = ''ZipPostalCode''
AND z.StoreId = 0
AND z.Value like ''%' + @ZipPostalCode + '%''' + @nl
END
--START WHERE CLAUSE
SELECT @sql += '
WHERE c.Deleted = 0' + @nl
IF NULLIF(@Email, '') IS NOT NULL
BEGIN
SELECT @sql += '
AND c.Email like ''%' + @Email + '%''' + @nl
END
IF NULLIF(@UserName, '') IS NOT NULL
BEGIN
SELECT @sql += '
AND c.UserName like ''%' + @UserName + '%''' + @nl
END
IF NULLIF(@CreatedFromUtc, '') IS NOT NULL
BEGIN
SELECT @sql += '
AND c.CreatedOnUtc >= ''' + CONVERT(varchar,@CreatedFromUtc) + '''' + @nl
END
IF NULLIF(@CreatedToUtc, '') IS NOT NULL
BEGIN
SELECT @sql += '
AND c.CreatedOnUtc <= ''' + CONVERT(varchar, @CreatedToUtc) + '''' + @nl
END
IF @AffiliateId > 0
BEGIN
SELECT @sql += '
AND c.AffiliateId = ''' + CONVERT(nvarchar,@AffiliateId) + '''' + @nl
END
IF @VendorId > 0
BEGIN
SELECT @sql += '
AND c.VendorId = ''' + CONVERT(nvarchar,@VendorId) + '''' + @nl
END
IF NULLIF(@IpAddress, '') IS NOT NULL
BEGIN
SELECT @sql += '
AND c.LastIpAddress = ''' + @IpAddress + '''' + @nl
END
--START WHERE CLAUSE
SELECT @sql += '
ORDER BY c.Id DESC' + @nl
--PRINT @sql;
DECLARE @PagingResult TABLE
(
Id INT,
TotalCount INT
)
DECLARE @page_sql NVARCHAR(MAX);
SET @page_sql = '
;WITH pg AS (
' +
@sql
+ '
OFFSET ' + CONVERT(varchar, @PageSize) + ' * (' + CONVERT(varchar, @PageIndex) + ') ROWS
FETCH NEXT ' + CONVERT(varchar, @PageSize) + ' ROWS ONLY
), TempCount AS (
SELECT TotalCount FROM pg
)
SELECT Id, TotalCount
FROM pg;
';
--PRINT @page_sql;
INSERT INTO @PagingResult(Id, TotalCount)
EXEC sp_executesql @page_sql
--Set our count
SELECT TOP 1 @TotalRecords = TotalCount
FROM @PagingResult;
--Return results
SELECT c.*
FROM Customer c
INNER JOIN @PagingResult pg ON c.Id = pg.Id -- or EXISTS
ORDER BY c.Id DESC;
END
GO