I work on sql server i face issue when insert data it take 4 minute for insert 0 rows
why
and How to solve this problem ?
execution plan as below :
execution plan
first table
so how to solve this issue please
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]GOSET ANSI_PADDING ONGOALTER TABLE [Parts].[TradeCodes] ADD CONSTRAINT [DF__TradeCode__Creat__215D38B9] DEFAULT (getdate()) FOR [CreatedDate]GOALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingDoneFlag]GOALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingValueId]GOCREATE TABLE [dbo].[TPLNewData]([PartID] [int] NOT NULL, [Code] [varchar](20) NULL, [CodeTypeID] [int] NULL, [RevisionID] [bigint] NULL, [ZPLID] [int] NULL)
ON [PRIMARY]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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TradeCodesInsert] ADD CONSTRAINT [DF__TradeCode__Creat__215D38B9] DEFAULT (getdate()) FOR [CreatedDate]GOALTER TABLE [dbo].[TradeCodesInsert] ADD DEFAULT ((0)) FOR [MappingDoneFlag]GOALTER TABLE [dbo].[TradeCodesInsert] ADD DEFAULT ((0)) FOR [MappingValueId]
GO
ahmed elbarbaryPosted Nov 19, 2021, 11:02 AM
parts.tradecodes have 150 milion rows
table ExtractReports.dbo.TPLNewData have 500000 rows
table trade code insert 100000 rows
on tables tradecodes and tradecode insert i have unique index on (partid,codetype,partlevel)
plnew data table not have index
Srinivasan RamamoorthiPosted Nov 19, 2021, 7:08 AM
Before inserting can you run select query and check to insert or update. For select query it shouldn’t take that much time.
how Many records, keys and indexes you have access in that table?