CODEWITHSUNDEEP
sql-server
VahiD
VahiD

Reputation: 1064

The query is very slow when a specific column is in SELECT statement

I have a generated SQL query (Entity Framework generated the query) which is very slow when I have an ORDER BY clause. After investigating so much, interestingly I found out that whenever I have UserName column in the SELECT's column list, the query became very slow (55 seconds), but if I select whatever column from the same table except the UserName, the query responds in milliseconds.

This is the query

SELECT 
    [r].[Amount], [r].[DeductedAmount], [r].[LoyaltyPoints], 
    [r].[ClientTransactionId], [r].[Quantity], [r].[RechargeTypeId], 
    [r0].[Name], [r].[ReferenceNo], [r].[TransactionStatusId], 
    [t].[Name] AS [Name0], [r].[TransactionDateTime], [r].[TransactionDate], 
    [r].[ResultCode], [u].UserName as username, [r].[ResultMessage], 
    [r].[Id], [s].[Id] AS [Id0], [u].[Id] AS [Id1], [r0].[Id] AS [Id2], 
    [t].[Id] AS [Id3]    
FROM 
    [Recharge].[RechargeTransaction] AS [r]
INNER JOIN 
    [Sales].[SalesPoint] AS [s] ON [r].[SalesPointId] = [s].[Id]
INNER JOIN 
    [Account].[User] AS [u] ON [s].[UserId] = [u].[Id]
INNER JOIN 
    [Recharge].[RechargeType] AS [r0] ON [r].[RechargeTypeId] = [r0].[Id]
INNER JOIN 
    [Recharge].[TransactionStatus] AS [t] ON [r].[TransactionStatusId] = [t].[Id]
WHERE 
    CHARINDEX(N'sample-username', [u].[UserName]) > 0
ORDER BY 
    [r].[ReferenceNo]

Noteworthy to mention that all of the primary keys are GUID with non-clustered indexes.

The UserName column is nvarchar(256) and has a unique index

Upvotes: 1

Views: 1032

Answers (1)

VahiD
VahiD

Reputation: 1064

after several investigation, I found out that if I reduced the column size to 100, the response time became milliseconds. This approach solved my problem, but I still don't know why the size of column is affecting the response time of query.

Upvotes: 1

Related Questions