I Work on sql server i have slow transfer data when make select into
small number of rows
it take too much time
my execution plan
https://www.brentozar.com/pastetheplan/?id=r1o3p8NOt
my query as below :
SELECT
d.PartID ,
d.Code ,
d.CodeTypeID ,
tr.RevisionID ,
tr.ZPLID,
tr.partlevel,
d.FeatureName,
d.FeatureValue
INTO ExtractReports.dbo.TEqualCodes
from ExtractReports.dbo.TAllData d with(nolock)
inner join parts.tradecodes tr with(nolock) on d.partid=tr.partid and d.codetypeid=tr.codetypeid and tr.partlevel=0 and d.code=tr.code and tr.zplid=4239
left join [ExtractReports].[dbo].[TradeCodesInsert] i with(nolock) on i.partid=tr.partid and i.codetypeid=tr.codetypeid and i.partlevel=tr.partlevel and i.partlevel=0 and tr.zplid=i.zplid
where i.partid is null
so what I do to enhance my query ?
What I have tried:
table structure
CREATE TABLE [Parts].[TradeCodes](
[TradeCodesID] [int] IDENTITY(1,1) NOT NULL,
[PartID] [int] NOT NULL,
[Code] [varchar](20) NOT NULL,
[CodeTypeID] [int] NOT NULL,
[SourceTypeID] [bigint] NULL,
[RevisionID] [bigint] NULL,
[ModifiedDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[Modifiedby] [int] NULL,
[CreatedBy] [int] NULL,
[PartLevel] [tinyint] NULL,
[ZPLID] [int] NULL,
[MappingDoneFlag] [int] NOT NULL,
[MappingValueId] [int] NOT NULL,
CONSTRAINT [PK__TradeCod__FEFAF27527F7A1C3] PRIMARY KEY CLUSTERED
(
[TradeCodesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UC_PartCode] UNIQUE NONCLUSTERED
(
[PartID] ASC,
[CodeTypeID] ASC,
[PartLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [Parts].[TradeCodes] ADD CONSTRAINT [DF__TradeCode__Creat__215D38B9] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingDoneFlag]
GO
ALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingValueId]
GO