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
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
November 3, 2021 at 1:16 pm
The missing index recommendation looks pretty good in this case -- you have a table scan because you are joining on PartLevel, but have no index.
Thank you for providing DDL scripts, but you missed dbo.PLNewData. Can you please provide the DDL for that table? PLNewData is a heap (no clustered index) that appears to have no indexes. I suspect a indexing that would help as well. It has only ~ 398 thousand rows, but estimate was 13.8 millions rows.
There are some experts on here that will probably be able to better suggest more/better improvements.
November 3, 2021 at 1:41 pm
To me it looks like your statistics are out of date. Your estimates are WAY off from the actuals which usually indicates bad statistics.
I would start there.
Another thing - I don't think it is the INSERT that is causing your slowness, it is the SELECT. I would run your SELECT query and determine the performance there and work to improve that.
Also, is NOLOCK required?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 3, 2021 at 6:17 pm
Not only is the SELECT slow - but it doesn't return any data to be inserted. You need to address the issues with that query - which is also accessing a table that doesn't appear in the query. It is because of that table that there are no results - you need to figure out how that table is being included and why there are no matches.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
You should indeed figure out if implementing the suggested missing index makes sense ( chances are ).
Maybe even add an index on your dbo.PLNewData table ( I've just put the column in random order, you should decide based on usage frequency and cardinality )
Create index x_PLNewData on dbo.PLNewData
(partid, codetypeid, code )
on top of that, you could test using "option force order" of your sql engine still doesn't handle it well enough.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply