Can I get rid of this HASH MATCH?

Transact-SQL
1

The query below is very CPU intensive. I've tried creating a variety of indexes and the plan does switch over to using the new indexes but it does not get rid of the HASH MATCH. Below is the query and the execution plan. I also attached a graphic of the execution plan.

(@p__linq__0 uniqueidentifier,@p__linq__1 uniqueidentifier,@p__linq__2 bit,@p__linq__3 nvarchar(4000),@p__linq__4 bit,@p__linq__5 nvarchar(4000),@p__linq__6 bit,@p__linq__7 nvarchar(4000))SELECT
[Project2].[ActiveStatus] AS [ActiveStatus],
[Project2].[Id] AS [Id],
[Project2].[FullName] AS [FullName],
[Project2].[EmailAddress] AS [EmailAddress],
[Project2].[Username] AS [Username]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Username] AS [Username],
[Extent1].[FullName] AS [FullName],
[Extent1].[ActiveStatus] AS [ActiveStatus],
[Extent1].[EmailAddress] AS [EmailAddress]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[IsDeleted] <> 1) AND ([Extent1].[ClientId] = @p__linq__0) AND (2 <> [Extent1].[ActiveStatus]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[xGroupUsers] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[User_Id]) AND ([Extent2].[Group_Id] = @p__linq__1)
)) AND (0 = [Extent1].[ActiveStatus]) AND (((@p__linq__2 = 1) AND ([Extent1].[FullName] LIKE @p__linq__3 ESCAPE N'~')) OR ((@p__linq__4 = 1) AND ([Extent1].[Username] LIKE @p__linq__5 ESCAPE N'~')) OR ((@p__linq__6 = 1) AND ([Extent1].[EmailAddress] LIKE @p__linq__7 ESCAPE N'~')))
) AS [Project2]
ORDER BY [Project2].[FullName] ASC, [Project2].[Id] ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

<?xml version="1.0" encoding="utf-16"?>

hashmatch
hashmatch.PNG723×245 14.2 KB

2

This look very similar to DB2.

This forum is dedicated to Microsoft SQL Server, so you might get better help on a DB2 forum.

4

It is tsql

5

This line doesn't work with Microsoft SQL Server:

OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

On Microsoft SQL Server we would use "top(20)", hence my comment on your query looks like DB2.

6

That syntax is supported in 2012+

7

My mistake. I have never seen it on Microsoft SQL Server, and couldn't get it to work:

select *
  from sys.all_objects
 offset 0 rows fetch next 20 rows only

gives:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '0'.
Msg 153, Level 15, State 2, Line 3
Invalid usage of the option next in the FETCH statement.

But throwing in an "order by" made it work:

select *
  from sys.all_objects
 order by name
 offset 0 rows fetch next 20 rows only

I learned something to day :slight_smile:

My appologies

1 Like