Andrei. I've checked, the reason why nopcommerce struggle to delete guest customers from task because of huge SQL request that I've tried to run separately in Management Studio.
5 minutes and still no result, a was need to stop it manually.
Under some circumstances (unfortunately I didn't check definitely which ones) the next query generated by linq in Nop.Services.Customers.CustomerService.DeleteGuestCustomer just runing for ages.
I believe at the point:
var customers = query.ToList();
line 652.
I think optimization required.
exec sp_executesql N'SELECT
[Project40].[Id] AS [Id],
[Project40].[Id1] AS [Id1],
[Project40].[CustomerGuid] AS [CustomerGuid],
[Project40].[Username] AS [Username],
[Project40].[Email] AS [Email],
[Project40].[Password] AS [Password],
[Project40].[PasswordFormatId] AS [PasswordFormatId],
[Project40].[PasswordSalt] AS [PasswordSalt],
[Project40].[AdminComment] AS [AdminComment],
[Project40].[IsTaxExempt] AS [IsTaxExempt],
[Project40].[AffiliateId] AS [AffiliateId],
[Project40].[VendorId] AS [VendorId],
[Project40].[Active] AS [Active],
[Project40].[Deleted] AS [Deleted],
[Project40].[IsSystemAccount] AS [IsSystemAccount],
[Project40].[SystemName] AS [SystemName],
[Project40].[LastIpAddress] AS [LastIpAddress],
[Project40].[CreatedOnUtc] AS [CreatedOnUtc],
[Project40].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project40].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project40].[CustomerStoreId] AS [CustomerStoreId],
[Project40].[BillingAddress_Id] AS [BillingAddress_Id],
[Project40].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project20].[Id] AS [Id],
[Limit1].[Id] AS [Id1],
[Limit1].[CustomerGuid] AS [CustomerGuid],
[Limit1].[Username] AS [Username],
[Limit1].[Email] AS [Email],
[Limit1].[Password] AS [Password],
[Limit1].[PasswordFormatId] AS [PasswordFormatId],
[Limit1].[PasswordSalt] AS [PasswordSalt],
[Limit1].[AdminComment] AS [AdminComment],
[Limit1].[IsTaxExempt] AS [IsTaxExempt],
[Limit1].[AffiliateId] AS [AffiliateId],
[Limit1].[VendorId] AS [VendorId],
[Limit1].[Active] AS [Active],
[Limit1].[Deleted] AS [Deleted],
[Limit1].[IsSystemAccount] AS [IsSystemAccount],
[Limit1].[SystemName] AS [SystemName],
[Limit1].[LastIpAddress] AS [LastIpAddress],
[Limit1].[CreatedOnUtc] AS [CreatedOnUtc],
[Limit1].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Limit1].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Limit1].[CustomerStoreId] AS [CustomerStoreId],
[Limit1].[BillingAddress_Id] AS [BillingAddress_Id],
[Limit1].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM (SELECT
[Distinct1].[Id] AS [Id]
FROM ( SELECT DISTINCT
[Project17].[Id] AS [Id]
FROM ( SELECT
[Project15].[Id] AS [Id],
[Project15].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project13].[Id] AS [Id],
[Project13].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project11].[Id] AS [Id],
[Project11].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project9].[Id] AS [Id],
[Project9].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project7].[Id] AS [Id],
[Project7].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project5].[Id] AS [Id],
[Project5].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Project3].[Id] AS [Id],
[Project3].[IsSystemAccount] AS [IsSystemAccount]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[IsSystemAccount] AS [IsSystemAccount]
FROM [dbo].[Customer] AS [Extent1]
LEFT OUTER JOIN [dbo].[Order] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CustomerId]
LEFT OUTER JOIN [dbo].[BlogComment] AS [Extent3] ON [Extent1].[Id] = [Extent3].[CustomerId]
LEFT OUTER JOIN [dbo].[NewsComment] AS [Extent4] ON [Extent1].[Id] = [Extent4].[CustomerId]
LEFT OUTER JOIN [dbo].[ProductReview] AS [Extent5] ON [Extent1].[Id] = [Extent5].[CustomerId]
LEFT OUTER JOIN [dbo].[ProductReviewHelpfulness] AS [Extent6] ON [Extent1].[Id] = [Extent6].[CustomerId]
LEFT OUTER JOIN [dbo].[PollVotingRecord] AS [Extent7] ON [Extent1].[Id] = [Extent7].[CustomerId]
LEFT OUTER JOIN [dbo].[Forums_Post] AS [Extent8] ON [Extent1].[Id] = [Extent8].[CustomerId]
LEFT OUTER JOIN [dbo].[Forums_Topic] AS [Extent9] ON [Extent1].[Id] = [Extent9].[CustomerId]
WHERE ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ShoppingCartItem] AS [Extent10]
WHERE [Extent1].[Id] = [Extent10].[CustomerId]
)) AND (@p__linq__0 >= [Extent1].[CreatedOnUtc]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Customer_CustomerRole_Mapping] AS [Extent11]
WHERE ([Extent1].[Id] = [Extent11].[Customer_Id]) AND ([Extent11].[CustomerRole_Id] = @p__linq__1)
))
) AS [Project3]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Order] AS [Extent12]
WHERE [Project3].[Id] = [Extent12].[CustomerId]
)
) AS [Project5]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[BlogComment] AS [Extent13]
WHERE [Project5].[Id] = [Extent13].[CustomerId]
)
) AS [Project7]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[NewsComment] AS [Extent14]
WHERE [Project7].[Id] = [Extent14].[CustomerId]
)
) AS [Project9]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ProductReview] AS [Extent15]
WHERE [Project9].[Id] = [Extent15].[CustomerId]
)
) AS [Project11]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ProductReviewHelpfulness] AS [Extent16]
WHERE [Project11].[Id] = [Extent16].[CustomerId]
)
) AS [Project13]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[PollVotingRecord] AS [Extent17]
WHERE [Project13].[Id] = [Extent17].[CustomerId]
)
) AS [Project15]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Forums_Post] AS [Extent18]
WHERE [Project15].[Id] = [Extent18].[CustomerId]
)
) AS [Project17]
WHERE ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Forums_Topic] AS [Extent19]
WHERE [Project17].[Id] = [Extent19].[CustomerId]
)) AND ([Project17].[IsSystemAccount] <> cast(1 as bit))
) AS [Distinct1] ) AS [Project20]
OUTER APPLY (SELECT TOP (1)
[Project37].[Id] AS [Id],
[Project37].[CustomerGuid] AS [CustomerGuid],
[Project37].[Username] AS [Username],
[Project37].[Email] AS [Email],
[Project37].[Password] AS [Password],
[Project37].[PasswordFormatId] AS [PasswordFormatId],
[Project37].[PasswordSalt] AS [PasswordSalt],
[Project37].[AdminComment] AS [AdminComment],
[Project37].[IsTaxExempt] AS [IsTaxExempt],
[Project37].[AffiliateId] AS [AffiliateId],
[Project37].[VendorId] AS [VendorId],
[Project37].[Active] AS [Active],
[Project37].[Deleted] AS [Deleted],
[Project37].[IsSystemAccount] AS [IsSystemAccount],
[Project37].[SystemName] AS [SystemName],
[Project37].[LastIpAddress] AS [LastIpAddress],
[Project37].[CreatedOnUtc] AS [CreatedOnUtc],
[Project37].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project37].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project37].[CustomerStoreId] AS [CustomerStoreId],
[Project37].[BillingAddress_Id] AS [BillingAddress_Id],
[Project37].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project35].[Id] AS [Id],
[Project35].[CustomerGuid] AS [CustomerGuid],
[Project35].[Username] AS [Username],
[Project35].[Email] AS [Email],
[Project35].[Password] AS [Password],
[Project35].[PasswordFormatId] AS [PasswordFormatId],
[Project35].[PasswordSalt] AS [PasswordSalt],
[Project35].[AdminComment] AS [AdminComment],
[Project35].[IsTaxExempt] AS [IsTaxExempt],
[Project35].[AffiliateId] AS [AffiliateId],
[Project35].[VendorId] AS [VendorId],
[Project35].[Active] AS [Active],
[Project35].[Deleted] AS [Deleted],
[Project35].[IsSystemAccount] AS [IsSystemAccount],
[Project35].[SystemName] AS [SystemName],
[Project35].[LastIpAddress] AS [LastIpAddress],
[Project35].[CreatedOnUtc] AS [CreatedOnUtc],
[Project35].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project35].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project35].[CustomerStoreId] AS [CustomerStoreId],
[Project35].[BillingAddress_Id] AS [BillingAddress_Id],
[Project35].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project33].[Id] AS [Id],
[Project33].[CustomerGuid] AS [CustomerGuid],
[Project33].[Username] AS [Username],
[Project33].[Email] AS [Email],
[Project33].[Password] AS [Password],
[Project33].[PasswordFormatId] AS [PasswordFormatId],
[Project33].[PasswordSalt] AS [PasswordSalt],
[Project33].[AdminComment] AS [AdminComment],
[Project33].[IsTaxExempt] AS [IsTaxExempt],
[Project33].[AffiliateId] AS [AffiliateId],
[Project33].[VendorId] AS [VendorId],
[Project33].[Active] AS [Active],
[Project33].[Deleted] AS [Deleted],
[Project33].[IsSystemAccount] AS [IsSystemAccount],
[Project33].[SystemName] AS [SystemName],
[Project33].[LastIpAddress] AS [LastIpAddress],
[Project33].[CreatedOnUtc] AS [CreatedOnUtc],
[Project33].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project33].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project33].[CustomerStoreId] AS [CustomerStoreId],
[Project33].[BillingAddress_Id] AS [BillingAddress_Id],
[Project33].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project31].[Id] AS [Id],
[Project31].[CustomerGuid] AS [CustomerGuid],
[Project31].[Username] AS [Username],
[Project31].[Email] AS [Email],
[Project31].[Password] AS [Password],
[Project31].[PasswordFormatId] AS [PasswordFormatId],
[Project31].[PasswordSalt] AS [PasswordSalt],
[Project31].[AdminComment] AS [AdminComment],
[Project31].[IsTaxExempt] AS [IsTaxExempt],
[Project31].[AffiliateId] AS [AffiliateId],
[Project31].[VendorId] AS [VendorId],
[Project31].[Active] AS [Active],
[Project31].[Deleted] AS [Deleted],
[Project31].[IsSystemAccount] AS [IsSystemAccount],
[Project31].[SystemName] AS [SystemName],
[Project31].[LastIpAddress] AS [LastIpAddress],
[Project31].[CreatedOnUtc] AS [CreatedOnUtc],
[Project31].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project31].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project31].[CustomerStoreId] AS [CustomerStoreId],
[Project31].[BillingAddress_Id] AS [BillingAddress_Id],
[Project31].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project29].[Id] AS [Id],
[Project29].[CustomerGuid] AS [CustomerGuid],
[Project29].[Username] AS [Username],
[Project29].[Email] AS [Email],
[Project29].[Password] AS [Password],
[Project29].[PasswordFormatId] AS [PasswordFormatId],
[Project29].[PasswordSalt] AS [PasswordSalt],
[Project29].[AdminComment] AS [AdminComment],
[Project29].[IsTaxExempt] AS [IsTaxExempt],
[Project29].[AffiliateId] AS [AffiliateId],
[Project29].[VendorId] AS [VendorId],
[Project29].[Active] AS [Active],
[Project29].[Deleted] AS [Deleted],
[Project29].[IsSystemAccount] AS [IsSystemAccount],
[Project29].[SystemName] AS [SystemName],
[Project29].[LastIpAddress] AS [LastIpAddress],
[Project29].[CreatedOnUtc] AS [CreatedOnUtc],
[Project29].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project29].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project29].[CustomerStoreId] AS [CustomerStoreId],
[Project29].[BillingAddress_Id] AS [BillingAddress_Id],
[Project29].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project27].[Id] AS [Id],
[Project27].[CustomerGuid] AS [CustomerGuid],
[Project27].[Username] AS [Username],
[Project27].[Email] AS [Email],
[Project27].[Password] AS [Password],
[Project27].[PasswordFormatId] AS [PasswordFormatId],
[Project27].[PasswordSalt] AS [PasswordSalt],
[Project27].[AdminComment] AS [AdminComment],
[Project27].[IsTaxExempt] AS [IsTaxExempt],
[Project27].[AffiliateId] AS [AffiliateId],
[Project27].[VendorId] AS [VendorId],
[Project27].[Active] AS [Active],
[Project27].[Deleted] AS [Deleted],
[Project27].[IsSystemAccount] AS [IsSystemAccount],
[Project27].[SystemName] AS [SystemName],
[Project27].[LastIpAddress] AS [LastIpAddress],
[Project27].[CreatedOnUtc] AS [CreatedOnUtc],
[Project27].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project27].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project27].[CustomerStoreId] AS [CustomerStoreId],
[Project27].[BillingAddress_Id] AS [BillingAddress_Id],
[Project27].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project25].[Id] AS [Id],
[Project25].[CustomerGuid] AS [CustomerGuid],
[Project25].[Username] AS [Username],
[Project25].[Email] AS [Email],
[Project25].[Password] AS [Password],
[Project25].[PasswordFormatId] AS [PasswordFormatId],
[Project25].[PasswordSalt] AS [PasswordSalt],
[Project25].[AdminComment] AS [AdminComment],
[Project25].[IsTaxExempt] AS [IsTaxExempt],
[Project25].[AffiliateId] AS [AffiliateId],
[Project25].[VendorId] AS [VendorId],
[Project25].[Active] AS [Active],
[Project25].[Deleted] AS [Deleted],
[Project25].[IsSystemAccount] AS [IsSystemAccount],
[Project25].[SystemName] AS [SystemName],
[Project25].[LastIpAddress] AS [LastIpAddress],
[Project25].[CreatedOnUtc] AS [CreatedOnUtc],
[Project25].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project25].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project25].[CustomerStoreId] AS [CustomerStoreId],
[Project25].[BillingAddress_Id] AS [BillingAddress_Id],
[Project25].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Project23].[Id] AS [Id],
[Project23].[CustomerGuid] AS [CustomerGuid],
[Project23].[Username] AS [Username],
[Project23].[Email] AS [Email],
[Project23].[Password] AS [Password],
[Project23].[PasswordFormatId] AS [PasswordFormatId],
[Project23].[PasswordSalt] AS [PasswordSalt],
[Project23].[AdminComment] AS [AdminComment],
[Project23].[IsTaxExempt] AS [IsTaxExempt],
[Project23].[AffiliateId] AS [AffiliateId],
[Project23].[VendorId] AS [VendorId],
[Project23].[Active] AS [Active],
[Project23].[Deleted] AS [Deleted],
[Project23].[IsSystemAccount] AS [IsSystemAccount],
[Project23].[SystemName] AS [SystemName],
[Project23].[LastIpAddress] AS [LastIpAddress],
[Project23].[CreatedOnUtc] AS [CreatedOnUtc],
[Project23].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Project23].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Project23].[CustomerStoreId] AS [CustomerStoreId],
[Project23].[BillingAddress_Id] AS [BillingAddress_Id],
[Project23].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM ( SELECT
[Extent20].[Id] AS [Id],
[Extent20].[CustomerGuid] AS [CustomerGuid],
[Extent20].[Username] AS [Username],
[Extent20].[Email] AS [Email],
[Extent20].[Password] AS [Password],
[Extent20].[PasswordFormatId] AS [PasswordFormatId],
[Extent20].[PasswordSalt] AS [PasswordSalt],
[Extent20].[AdminComment] AS [AdminComment],
[Extent20].[IsTaxExempt] AS [IsTaxExempt],
[Extent20].[AffiliateId] AS [AffiliateId],
[Extent20].[VendorId] AS [VendorId],
[Extent20].[Active] AS [Active],
[Extent20].[Deleted] AS [Deleted],
[Extent20].[IsSystemAccount] AS [IsSystemAccount],
[Extent20].[SystemName] AS [SystemName],
[Extent20].[LastIpAddress] AS [LastIpAddress],
[Extent20].[CreatedOnUtc] AS [CreatedOnUtc],
[Extent20].[LastLoginDateUtc] AS [LastLoginDateUtc],
[Extent20].[LastActivityDateUtc] AS [LastActivityDateUtc],
[Extent20].[CustomerStoreId] AS [CustomerStoreId],
[Extent20].[BillingAddress_Id] AS [BillingAddress_Id],
[Extent20].[ShippingAddress_Id] AS [ShippingAddress_Id]
FROM [dbo].[Customer] AS [Extent20]
LEFT OUTER JOIN [dbo].[Order] AS [Extent21] ON [Extent20].[Id] = [Extent21].[CustomerId]
LEFT OUTER JOIN [dbo].[BlogComment] AS [Extent22] ON [Extent20].[Id] = [Extent22].[CustomerId]
LEFT OUTER JOIN [dbo].[NewsComment] AS [Extent23] ON [Extent20].[Id] = [Extent23].[CustomerId]
LEFT OUTER JOIN [dbo].[ProductReview] AS [Extent24] ON [Extent20].[Id] = [Extent24].[CustomerId]
LEFT OUTER JOIN [dbo].[ProductReviewHelpfulness] AS [Extent25] ON [Extent20].[Id] = [Extent25].[CustomerId]
LEFT OUTER JOIN [dbo].[PollVotingRecord] AS [Extent26] ON [Extent20].[Id] = [Extent26].[CustomerId]
LEFT OUTER JOIN [dbo].[Forums_Post] AS [Extent27] ON [Extent20].[Id] = [Extent27].[CustomerId]
LEFT OUTER JOIN [dbo].[Forums_Topic] AS [Extent28] ON [Extent20].[Id] = [Extent28].[CustomerId]
WHERE ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ShoppingCartItem] AS [Extent29]
WHERE [Extent20].[Id] = [Extent29].[CustomerId]
)) AND (@p__linq__0 >= [Extent20].[CreatedOnUtc]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Customer_CustomerRole_Mapping] AS [Extent30]
WHERE ([Extent20].[Id] = [Extent30].[Customer_Id]) AND ([Extent30].[CustomerRole_Id] = @p__linq__1)
))
) AS [Project23]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Order] AS [Extent31]
WHERE [Project23].[Id] = [Extent31].[CustomerId]
)
) AS [Project25]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[BlogComment] AS [Extent32]
WHERE [Project25].[Id] = [Extent32].[CustomerId]
)
) AS [Project27]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[NewsComment] AS [Extent33]
WHERE [Project27].[Id] = [Extent33].[CustomerId]
)
) AS [Project29]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ProductReview] AS [Extent34]
WHERE [Project29].[Id] = [Extent34].[CustomerId]
)
) AS [Project31]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ProductReviewHelpfulness] AS [Extent35]
WHERE [Project31].[Id] = [Extent35].[CustomerId]
)
) AS [Project33]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[PollVotingRecord] AS [Extent36]
WHERE [Project33].[Id] = [Extent36].[CustomerId]
)
) AS [Project35]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Forums_Post] AS [Extent37]
WHERE [Project35].[Id] = [Extent37].[CustomerId]
)
) AS [Project37]
WHERE ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Forums_Topic] AS [Extent38]
WHERE [Project37].[Id] = [Extent38].[CustomerId]
)) AND ([Project37].[IsSystemAccount] <> cast(1 as bit)) AND ([Project20].[Id] = [Project37].[Id]) ) AS [Limit1]
) AS [Project40]
ORDER BY [Project40].[Id1] ASC',N'@p__linq__0 datetime2(7),@p__linq__1 int',@p__linq__0='2014-03-17 09:04:40.2311701',@p__linq__1=4