Processor intensive SQL Query

We are debugging performance issues with our Nop installation and have found this query to be consuming a lot of processor time

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].[HasShoppingCartItems] AS [HasShoppingCartItems],       [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].[HasShoppingCartItems] AS [HasShoppingCartItems],           [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


Does anyone know where its called in the code and how we might be able to improve performance.

Darren

You are having this issue during nop installation?

No. During normal usage of our site inkredible.co.uk

I'm running a trace using SQL Profiler.

Most queries have a duration of 0 - 40. That query has a duration of over 500.
Most queries have cpu figure of 0-40. That query has one of over 4000!

wunpac wrote:
Hi Darren,

What is this parameter set to?

Hi,

Here is the full TextData. Is this what you need?

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].[HasShoppingCartItems] AS [HasShoppingCartItems],
    [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].[HasShoppingCartItems] AS [HasShoppingCartItems],
        [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'

Tyr executing this SQL command manually (using SSMS) over your database. Does it return any record?

Sorry, you're dealing with an SQL novice. How do I do that?

I've tried copying the exact query (above) into a "New Query" in ssms but the syntax isn't correct (squiggly red underlines).

Could you spell it out to me please.

You can do it in SSMS - https://www.youtube.com/watch?v=YgyO2NN5wNA

I get

must declare the scalar variable "@p__linq__0"

you should execute this SQL command (not this one)