Reputation: 1064
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
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