What I do to enhance this statement select into because it take too much time for 2000 rows?-CodePudding

I work on SQL Server I have slow transfer data when make select into table, it takes too much time to transfer small number (2000) of rows

This is my execution plan:

https://www.brentozar.com/pastetheplan/?id=r1o3p8NOt

And this is my query:

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

Table structure of two tables trade codes and trade codes insert

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]

CREATE TABLE ExtractReports.dbo.TAllData
(
    PartID INT,
    Code VARCHAR(20),
    CodeTypeID INT,
    RevisionID BIGINT,
    ZPLID INT,
    ConCount INT,
    FeatureName NVARCHAR(500),
    FeatureValue NVARCHAR(500)
)

How to solve issue of slow on query above?

CodePudding user response：

Create EXACTLY those indexs :

CREATE INDEX X1 ON [dbo].[TradeCodesInsert] (partid, codetypeid, partlevel);
CREATE INDEX X2 ON parts.tradecodes  (codetypeid, partlevel, zplid, partid) INCLUDE (RevisionID);
CREATE INDEX X2 ON ExtractReports.dbo.TAllData (code, codetypeid) INCLUDE (PartID , FeatureName, FeatureValue)

If you do not have it actually.