We run a large nopCommerce site with roughly 115,000 registered customers and counting. With this large data set we've noticed a major performance issue when searching for customers on the admin side by First Name, Last Name, Company or any of the other Generic Attribute fields associated to the customer. I know we don't want every generic attribute added to the customer record in the database but I feel like the site could easily and quickly benefit from moving the required attributes "First Name" and "Last Name" to the customer record as well as the optional "Company" field. The other fields could continue to be Generic attributes.
For us we typically search on Email, First and Last Name, or Company when trying to track down customers and I would imagine most store owners would do the same. The performance gain would be massive when searching and would also benefit the front end with faster load times when retrieving customer data throughout the site.
Also, another thing to consider is creating a stored procedure to perform the search. Similar to how it's down for products.
Let me know your thoughts...
Customer Search (Peformance Optimization)
Certified Developer
- 57
- 491
- 8/13/2013
- United States
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.
And the stored Procedure
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
, @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