Insert rows it take too much time for 0 rows inserted?

Transact-SQL
,
1

I work on SQL server 2012 I face issue when insert rows it take too much time
and on final no rows added

EXECUTION PLAN AS BELOW

tables script

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]

table that insert on it

USE [ExtractReports]
 GO
    
 /****** Object:  Table [dbo].[TradeCodesInsert]    Script Date: 11/3/2021 9:44:54 AM ******/
 SET ANSI_NULLS ON
 GO
    
 SET QUOTED_IDENTIFIER ON
 GO
    
 SET ANSI_PADDING ON
 GO
    
 CREATE TABLE [dbo].[TradeCodesInsert](
     [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,
     [FunctionName] [nvarchar](50) NULL,
     [MappingDoneFlag] [int] NOT NULL,
     [MappingValueId] [int] NOT NULL,
     [FeatureName] [nvarchar](500) NULL,
     [FeatureValue] [nvarchar](500) NULL,
     [FlagInsert] [int] 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 OFF
 GO
    
 ALTER TABLE [dbo].[TradeCodesInsert] ADD  CONSTRAINT [DF__TradeCode__Creat__215D38B9]  DEFAULT (getdate()) FOR [CreatedDate]
 GO
    
 ALTER TABLE [dbo].[TradeCodesInsert] ADD  DEFAULT ((0)) FOR [MappingDoneFlag]
 GO
    
 ALTER TABLE [dbo].[TradeCodesInsert] ADD  DEFAULT ((0)) FOR [MappingValueId]
 GO
2
  1. Does dbo.TradeCodesInsert have any triggers?
  2. Are there any indexed views which include dbo.TradeCodesInsert?
  3. Have you checked for blocking?
    etc
3

thank you for reply
1- 1. Does dbo.TradeCodesInsert have any triggers?
No
2- 1. Are there any indexed views which include dbo.TradeCodesInsert?
unique constrain and cluster index on Tradecodesid
3-Have you checked for blocking?
How to check blocking

4
  1. Fine.
  2. Q: How much caffine do you need? A. I am 6ft tall.
  3. How to identify blocking in SQL Server
1 Like
5

i check nothing block me

6

can you provide the insert statements that are taking a long time. Seems that you have something else going on there; either triggers or cascade Fks?

7

So what is your question - is it that your query returns 0 rows or that it takes too long?

The query takes a long time because it is performing an index scan on the table TradeCodes (over 10 million rows), a table scan on PLNewData (over 380,000 rows) - joining those tables to return 14,224 rows. Those results are then sorted in order to perform a join with the table UC_PartCode which doesn't find any matches.

Further to that - the query doesn't reference that lookup table directly, so either there is more to the query that isn't shown or something else is missing that we cannot see.

As to why it doesn't return any data - as stated above, no matches found on the part ID and the 'join' to the UC_PartCode table (wherever that is defined).

8

for the heck of it, did you try the suggested index?