Databases
StackOverflow2013
Postsdbo
POHow to improve this query to avoid hash match
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to improve this query to avoid hash match
    text
    copied!<p>I have q query to select ID of duplicated rows. If the provinceID is same and at least one phonenumber is same, then we can determine that two rows are identicial. </p> <pre><code>SELECT i1.ID FROM pigeon.dbo.Instrument i1 WHERE EXISTS( SELECT i2.ID FROM pigeon.dbo.Instrument i2 WHERE i2.ID != i1.ID and i2.ProvinceID = i1.ProvinceID and ( (RTRIM(i2.PhoneNumber1) != '' and (RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber3))) or (RTRIM(i2.PhoneNumber2) != '' and (RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber3))) or (RTRIM(i2.PhoneNumber3) != '' and (RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber3))) ) ) </code></pre> <p>The query excution plan </p> <p><img src="https://i.stack.imgur.com/p2q59.png" alt="enter image description here"></p> <p>Whilst the other similar query performs very well, and no hash match in execution plan. The query is </p> <pre><code>SELECT i1.ID FROM pigeon.dbo.Instrument i1 WHERE EXISTS( SELECT i2.ID FROM pigeon.dbo.Instrument i2 WHERE i2.ID != i1.ID and i2.Name = i1.Name and ( (RTRIM(i2.PhoneNumber1) != '' and (RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber1) = RTRIM(i1.PhoneNumber3))) or (RTRIM(i2.PhoneNumber2) != '' and (RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber2) = RTRIM(i1.PhoneNumber3))) or (RTRIM(i2.PhoneNumber3) != '' and (RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber1) or RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber2) or RTRIM(i2.PhoneNumber3) = RTRIM(i1.PhoneNumber3))) ) ) </code></pre> <p>The execution plan is </p> <p><img src="https://i.stack.imgur.com/pRdFW.png" alt="enter image description here"></p> <p>I created index for all fields which is included in where clause. </p> <p><img src="https://i.stack.imgur.com/pJmkd.png" alt="enter image description here"></p> <p>The table is defined as </p> <pre><code>CREATE TABLE [dbo].[Instrument]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, [Gender] [char](1) NULL, [Birthdate] [datetime] NULL, [PhoneNumber1] [nvarchar](50) NULL, [PhoneNumber2] [nvarchar](50) NULL, [PhoneNumber3] [nvarchar](50) NULL, [Email] [nvarchar](64) NULL, [Address] [nvarchar](250) NULL, [IDType] [smallint] NOT NULL, [IDNumber] [nchar](32) NULL, [ProvinceID] [char](16) NOT NULL, [CityID] [char](16) NOT NULL, [Blacklist] [bit] NULL, [BeenProject] [nvarchar](64) NULL, [BeenCity] [nvarchar](50) NULL, [BeenDate] [datetime] NULL, [BeenRemark] [nvarchar](250) NULL, [PlateType] [smallint] NOT NULL, [PlateNumber] [nchar](16) NULL, [Color] [nchar](16) NULL, [Vendor] [nvarchar](64) NULL, [ModelID] [bigint] NOT NULL, [Version] [nvarchar](64) NULL, [Level] [nvarchar](64) NULL, [ModelEx] [nvarchar](250) NULL, [Van] [nvarchar](64) NULL, [Volume] [nvarchar](64) NULL, [EngineNumber] [nvarchar](64) NULL, [VINNumber] [nvarchar](64) NULL, [ShipmentDate] [datetime] NULL, [BoughtDate] [datetime] NULL, [RegisteDate] [datetime] NULL, [Remark1] [nvarchar](500) NULL, [Remark2] [nvarchar](500) NULL, [Remark3] [nvarchar](500) NULL, [Remark4] [nvarchar](500) NULL, [Remark5] [nvarchar](500) NULL, [Referer] [nvarchar](64) NULL, [Shared] [bit] NOT NULL, [Counter] [smallint] NOT NULL, [XRefID] [bigint] NULL, [Active] [bit] NOT NULL, [CreatedBy] [nchar](32) NOT NULL, [CreatedAt] [datetime] NOT NULL, [UpdatedBy] [nchar](32) NULL, [UpdatedAt] [datetime] NULL </code></pre> <p>)</p> <p>Why their's execution plan is so different? The second query's performance is very good compare to the first query. Please help to improve the performance of the first query. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload