Processor intensive SQL Query

Thanks for you detailed reply. I may implement your code for other reasons, but the problem we are having here is not with guest records. It's with search engine bots. The slow query is the search for the built-in search engine record

  customer = _customerService.GetCustomerBySystemName(SystemCustomerNames.SearchEngine);

Could we somehow cache the searchengine customer object so its really quick when a bot goes to the site?

I've commented this out

     if (customer == null || customer.Deleted || !customer.Active)
                {
                    if (_userAgentHelper.IsSearchEngine())
                        customer = _customerService.GetCustomerBySystemName(SystemCustomerNames.SearchEngine);
                }

I suspect server response times for search engines will be a lot quicker now too.

I'll let you know once we've put this change live and run pagespeed

So, despite commenting out the above code we are still seeing this query being executed.

exec sp_executesql N'SELECT TOP (1) 
    [Project1].[Id] AS [Id], 
    [Project1].[CustomerGuid] AS [CustomerGuid], 
    [Project1].[Username] AS [Username], 
    [Project1].[Email] AS [Email], 
    [Project1].[Password] AS [Password], 
    [Project1].[PasswordFormatId] AS [PasswordFormatId], 
    [Project1].[PasswordSalt] AS [PasswordSalt], 
    [Project1].[AdminComment] AS [AdminComment], 
    [Project1].[IsTaxExempt] AS [IsTaxExempt], 
    [Project1].[AffiliateId] AS [AffiliateId], 
    [Project1].[VendorId] AS [VendorId], 
    [Project1].[Active] AS [Active], 
    [Project1].[Deleted] AS [Deleted], 
    [Project1].[IsSystemAccount] AS [IsSystemAccount], 
    [Project1].[SystemName] AS [SystemName], 
    [Project1].[LastIpAddress] AS [LastIpAddress], 
    [Project1].[CreatedOnUtc] AS [CreatedOnUtc], 
    [Project1].[LastLoginDateUtc] AS [LastLoginDateUtc], 
    [Project1].[LastActivityDateUtc] AS [LastActivityDateUtc], 
    [Project1].[BillingAddress_Id] AS [BillingAddress_Id], 
    [Project1].[ShippingAddress_Id] AS [ShippingAddress_Id]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[CustomerGuid] AS [CustomerGuid], 
        [Extent1].[Username] AS [Username], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[Password] AS [Password], 
        [Extent1].[PasswordFormatId] AS [PasswordFormatId], 
        [Extent1].[PasswordSalt] AS [PasswordSalt], 
        [Extent1].[AdminComment] AS [AdminComment], 
        [Extent1].[IsTaxExempt] AS [IsTaxExempt], 
        [Extent1].[AffiliateId] AS [AffiliateId], 
        [Extent1].[VendorId] AS [VendorId], 
        [Extent1].[Active] AS [Active], 
        [Extent1].[Deleted] AS [Deleted], 
        [Extent1].[IsSystemAccount] AS [IsSystemAccount], 
        [Extent1].[SystemName] AS [SystemName], 
        [Extent1].[LastIpAddress] AS [LastIpAddress], 
        [Extent1].[CreatedOnUtc] AS [CreatedOnUtc], 
        [Extent1].[LastLoginDateUtc] AS [LastLoginDateUtc], 
        [Extent1].[LastActivityDateUtc] AS [LastActivityDateUtc], 
        [Extent1].[BillingAddress_Id] AS [BillingAddress_Id], 
        [Extent1].[ShippingAddress_Id] AS [ShippingAddress_Id]
        FROM [dbo].[Customer] AS [Extent1]
        WHERE ([Extent1].[SystemName] = @p__linq__0) OR (([Extent1].[SystemName] IS NULL) AND (@p__linq__0 IS NULL))
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'SearchEngine'

Is there somewhere else that runs a query to look for the search engine record?

It's the only one I see.   I can only suggest that you clean/re-build the solution, then delete temp ASP.net files, and redeploy.

We've done that quite a few time over the last few weeks as part of other updates. The server has even been restarted and the app moved to a new app pool. Also, there is only 1 nop site on the server so it must be coming from the code where I did the commenting out.

Any other ideas?

Worked it out. We had a rouge site running on Azure that connected to a Db on the server.

Doh!