when insert rows it take too much time for 0 rows inserted so why and How to solve that ?

Question

question

ahmedsalah-1628 asked Nov 3, '21 MelissaMa-msft edited Nov 11, '21

when insert rows it take too much time for 0 rows inserted so why and How to solve that ?

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

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

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

sql-server-general sql-server-transact-sql

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft · Nov 04, 2021 at 08:21 AM

Hi @ahmedsalah-1628,

Could you please provide any update?

If the answer is not helpful, please provide DDL and sample data together with updated execution plan so that we could check further.

Thank you!

Best regards,
Melissa

0 ·

Answer 1 · 2021-11-03T08:34:26.673Z

MelissaMa-msft answered Nov 3, '21

Hi @ahmedsalah-1628,

Only according to your execution plan and the suggestion automatically created, you could create one index like below on parts.tradecodes.

   CREATE NONCLUSTERED 
  INDEX tradecodes_partlevel
  ON parts.tradecodes ([partlevel])
  INCLUDE ([PartID],[Code],[CodeTypeID])

You could also create indexes on dbo.PLNewData like below:

  create clustered index ix_partid on [ExtractReports].dbo.PLNewData(partid)
   create index ix_codetypeid on [ExtractReports].dbo.PLNewData(codetypeid)

If it is still not helpful after doing above, please provide the DDL of [ExtractReports].dbo.PLNewData and some sample data for all tables which would help us check further.

Best regards,
Melissa

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

exec1.png (49.6 KiB)

exec2.png (35.5 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question