I work on sql server 2019 i face issue when select top 1 it take too much time
although it one record but it take too much time may be 3 minutes
this table extractreports.[dbo].[FinalTechnologyGeneration] have 32000 rows 32 thousand rows
and table [Technology].[PartsGeneratedRules] have 600000 thousand rows 600 thousand rows
i only make select top 1 where message rule string field =generategrouprule string field
so how to make select top 1 more faster and take time less than3 minutes
so why select is very slow although i select top 1 only
sample ddl
CREATE TABLE [dbo].[FinalTechnologyGeneration](
[TechnologyId] [bigint] NULL,
[PartID] [int] NULL,
[PLID] [int] NULL,
[CodeRulesId] [int] NULL,
[GroupCode] [nvarchar](5) NOT NULL,
[MessageRule] [varchar](800) NULL,
[GroupCodeRank] [nvarchar](5) NULL
) ON [PRIMARY]
CREATE TABLE [Technology].[PartsGeneratedRules](
[PartId] [int] NOT NULL,
[CodeRulesId] [int] NULL,
[GeneratedGroupCode] [nvarchar](10) NULL,
[GeneratedGroupRule] [nvarchar](500) NULL,
[ModifiedDate] [datetime] NULL,
[Modifiedby] [int] NULL,
[PLID] [int] NULL,
[TechnologyId] [int] NULL,
[GroupCodeRank] [int] NULL,
PRIMARY KEY CLUSTERED
(
[PartId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
What I have tried:
select top 1 1 from extractreports.dbo.FinalTechnologyGeneration f with(nolock)
inner join [Technology].[PartsGeneratedRules] r with(nolock) on f.MessageRule=r.GeneratedGroupRule