ahmed elbarbary's questions

select statment我在 sql server 2012 上工作，我遇到了从上个月到本月无法获取 Partid 的问题march (3)。

基于last date存在每partid

并在同一时间，如果有的话，gaps between dates然后根据最后日期填写

因此，如果

我找到了最后日期为 2022-01-08 的 partid，然后我将添加08-02-2022并08-03-2022作为 partid6070

如果partid在一个月7和下个月有日期，并且在第 8个月10没有日期part id

和 9 然后它必须gaps根据上个月显示这个，因为 partid 1234 有 gap 。

这两种情况都必须适用于所有基于partid.

Createddate下面在甲酸盐上使用yyyy-mm-dd。

 create table Parts
 (
    
 PartId int,
 CreatedDate date
 )
 insert into Parts(PartId,CreatedDate)
 values
 (1234,'2021-07-03'),
 (1234,'2021-10-05'),
 (1234,'2021-11-05'),
 (5981,'2021-11-15'),
 (5981,'2021-12-03'),
 (6070,'2021-12-12'),
 (6070,'2022-01-08')

我需要按预期结果select statment显示parts

green rows只是为了更清楚，这些零件必须在旧零件添加之前存在。

预期结果

我尝试什么

更新后的帖子

下面的代码给了我预期结果的一部分

因为它给了我剩余日期之间的空白以获取当前月份的日期

那么该怎么做呢？

;with cte as (
   select partid, createddate,
          dateadd(month, -1,
                  coalesce(lead(createddate) over (partition by partid order by createddate),
                           max(createddate) over ()
                          )
                 ) as end_month
   from Parts
   union all
   select partid, dateadd(month, 1, createddate), end_month
   from cte
   where createddate <end_month
  )
 select *
 from cte
 order by partid, createddate

我在 sql server 2019 上工作

更新表时Z2DataCore.parts.SourcingNotMappedParts 我发现​​更新时缓慢而长时间

预计执行计划

实际执行计划

更新语句需要 26.5 分钟才能完成对 692488 行计数的更新，因此 How to make Faster 。

语句生成缓慢的过程如下：

UPDATE s 
SET s.PriorityLevel = 'I2'   
FROM Z2DataCore.parts.SourcingNotMappedParts s 
INNER JOIN extractreports.dbo.alldistSuppliersourceid g
    ON g.SourcingNotMappedPartsID = s.SourcingNotMappedPartsID

受影响的更新行数将是 692488

我需要更新的表 SourcingNotMappedParts 通常有 7100 万行。

extractreports.dbo.SourcingNotMappedPartsIDI1我将从它获取要更新的数据的表是 692488 。

表extractreports.dbo.SourcingNotMappedPartsIDI1只有一SourcingNotMappedPartsID列int datatype

表extractreports.dbo.SourcingNotMappedPartsIDI1只有一个索引如下

 CREATE clustered INDEX SourcingNotMappedPartsIDI1_IDX ON extractreports.dbo.SourcingNotMappedPartsIDI1(SourcingNotMappedPartsID)



USE [Z2DataCore]
 GO
 /****** Object:  Table [Parts].[SourcingNotMappedParts]    Script Date: 3/4/2022 12:05:36 AM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [Parts].[SourcingNotMappedParts](
  [SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
  [SearchPart] [nvarchar](200) NULL,
  [GivenManufacture] [nvarchar](200) NULL,
  [CompanyId] [int] NULL,
  [SourceTypeID] [int] NULL,
  [PartStatus] [nvarchar](50) NULL,
  [StockId] [int] NULL,
  [SourceUrl] [nvarchar](2000) NULL,
  [PartId] [int] NULL,
  [GroupID] [int] NULL,
  [PartStatusID] [int] NULL,
  [MatchStatus] [nvarchar](200) NULL,
  [GivenPartNumber_Non] [nvarchar](200) NULL,
  [GivenManufacturer_Non] [nvarchar](200) NULL,
  [signatureID] [int] NULL,
  [VCompanyId] [int] NULL,
  [PriorityLevel] [nvarchar](10) NULL,
  [NotMappedCode] [int] NULL,
  CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED 
 (
  [SourcingNotMappedPartsID] 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
 /****** Object:  Index [IDX_MatchStatus_StatusID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_MatchStatus_StatusID] ON [Parts].[SourcingNotMappedParts]
 (
  [PartStatusID] ASC,
  [MatchStatus] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 /****** Object:  Index [IDX_Part_status_ID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_Part_status_ID] ON [Parts].[SourcingNotMappedParts]
 (
  [PartStatusID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IDX_SourceURL]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourceURL] ON [Parts].[SourcingNotMappedParts]
 (
  [SourceUrl] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IDX_SourcingNotMappedParts_GroupID_SearchPart]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_GroupID_SearchPart] ON [Parts].[SourcingNotMappedParts]
 (
  [GroupID] ASC,
  [SearchPart] ASC
 )
 INCLUDE (  [signatureID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 /****** Object:  Index [IDX_SourcingNotMappedParts_PartId]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_PartId] ON [Parts].[SourcingNotMappedParts]
 (
  [PartId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 /****** Object:  Index [IDX_SourcingNotMappedParts_SignatureID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_SignatureID] ON [Parts].[SourcingNotMappedParts]
 (
  [signatureID] ASC
 )
 INCLUDE (  [PartId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_NonalphaPartCompany]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_NonalphaPartCompany] ON [Parts].[SourcingNotMappedParts]
 (
  [GivenPartNumber_Non] ASC,
  [VCompanyId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 /****** Object:  Index [IX_NotMapped_NotMappedCode]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_NotMappedCode] ON [Parts].[SourcingNotMappedParts]
 (
  [NotMappedCode] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_PriorityLevel]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_PriorityLevel] ON [Parts].[SourcingNotMappedParts]
 (
  [PriorityLevel] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 /****** Object:  Index [IX_NotMapped_SourceType]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_SourceType] ON [Parts].[SourcingNotMappedParts]
 (
  [SourceTypeID] ASC,
  [CompanyId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_VCompanyId_sourcetypeid]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts]
 (
  [VCompanyId] ASC,
  [SourceTypeID] ASC,
  [PriorityLevel] ASC
 )
 INCLUDE (  [GivenPartNumber_Non]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_SourcingNotMappedParts_VCompanyId]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_SourcingNotMappedParts_VCompanyId] ON [Parts].[SourcingNotMappedParts]
 (
  [VCompanyId] ASC
 )
 INCLUDE (  [CompanyId],
  [SourceTypeID],
  [StockId],
  [GivenPartNumber_Non],
  [PriorityLevel]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO

那么如何解决缓慢的问题呢？

我检查了更新状态，发现等待类型如下：

这与慢或不相关。

我在 sql server 2019 上工作，我在选择top 5 行时遇到问题，它需要太多时间。

表上的行数Z2DataCore.parts.SourcingNotMappedParts70 百万行。

当运行语句选择top 5它需要太多时间超过15 minutes。

那么如何让它更快

选择有问题的语句

SELECT top 5 GivenPartNumber_Non,vcompanyid
into #GetSupplierAndOther
FROM Z2DataCore.parts.SourcingNotMappedParts with(nolock)
Where  PriorityLevel in ('A3','A4') and vcompanyid is not null and sourcetypeid=484456
group by GivenPartNumber_Non,vcompanyid
having count(distinct sourcetypeid)=2

我估计的执行计划

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

注意：我尝试在不使用的情况下选择上面的列，select into但仍然很慢。

示例表脚本和索引

CREATE TABLE [Parts].[SourcingNotMappedParts](
    [SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
    [SearchPart] [nvarchar](200) NULL,
    [GivenManufacture] [nvarchar](200) NULL,
    [CompanyId] [int] NULL,
    [SourceTypeID] [int] NULL,
    [RevisionId] [bigint] NULL,
    [ExtractionDate] [date] NULL,
    [Taxonomy] [nvarchar](250) NULL,
    [PartStatus] [nvarchar](50) NULL,
    [Datasheet] [nvarchar](2000) NULL,
    [ROHS] [nvarchar](250) NULL,
    [StockId] [int] NULL,
    [SourceUrl] [nvarchar](2000) NULL,
    [Description] [nvarchar](2000) NULL,
    [CreatedBy] [int] NULL,
    [ModifiedBy] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [ModifiedDate] [datetime] NULL,
    [Comment] [nvarchar](2000) NULL,
    [Reason] [nvarchar](2000) NULL,
    [PartId] [int] NULL,
    [GroupID] [int] NULL,
    [PartStatusID] [int] NULL,
    [ManufactureStatus] [int] NULL,
    [EditStatus] [int] NULL,
    [FamilyID] [int] NULL,
    [LookupId] [int] NULL,
    [ValidationReasonId] [int] NULL,
    [MatchStatus] [nvarchar](200) NULL,
    [GivenPartNumber_Non] [nvarchar](200) NULL,
    [GivenManufacturer_Non] [nvarchar](200) NULL,
    [signatureID] [int] NULL,
    [VCompanyId] [int] NULL,
    [PriorityLevel] [nvarchar](10) NULL,
    [NotMappedCode] [int] NULL,
    [PCPartStatus] [nvarchar](50) NULL,
 CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED 
(
    [SourcingNotMappedPartsID] 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

ALTER TABLE [Parts].[SourcingNotMappedParts] ADD  CONSTRAINT [DF_SourcingNotMappedParts_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [Parts].[SourcingNotMappedParts] ADD  CONSTRAINT [DF_SourcingNotMappedParts_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

ALTER TABLE [Parts].[SourcingNotMappedParts] ADD  CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED 
(
    [SourcingNotMappedPartsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_NotMapped_SourceType] ON [Parts].[SourcingNotMappedParts]
(
    [SourceTypeID] ASC,
    [CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_NotMapped_PriorityLevel] ON [Parts].[SourcingNotMappedParts]
(
    [PriorityLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_NotMapped_NonalphaPartCompany] ON [Parts].[SourcingNotMappedParts]
(
    [GivenPartNumber_Non] ASC,
    [VCompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_VCompanyId] ON [Parts].[SourcingNotMappedParts]
(
    [VCompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我在 sql server 2017 上工作，我遇到了我无法解决的问题partnumber

至少具有源类型website并且至少具有stockid equal 1每个partnumber

所以还有另一个含义，我需要获得具有stockid 1和source type website

所以我需要进行选择查询获取必须具有源类型website和库存=1的零件编号Part number

样本数据

create table #stockyes
(
PartNumber varchar(100),
sourcetype varchar(100),
stockid tinyint
)
insert into #stockyes(PartNumber,sourcetype,stockid)
values
(1233,'Website',1),
(1233,'Website',0),
(1233,'Datasheet',1),

(8901,'Website',1),
(8901,'Website',0),

(9015,'Website',1),

(7801,'Website',1),
(7801,'Website',1),

(9401,'Datasheet',0),

(5688,'Datasheet',0),
(5688,'Datasheet',0),

(5688,'Lifecycle',2),
(5688,'Lifecycle',2),

(8409,'Lifecycle',1),

(6703,'Website',0),
(6703,'Website',0),
(6703,'Website',0),

(3099,'Website',0),
(3099,'Website',0),

(8541,'Website',0)

预期结果

我正在使用 SQL Server 2019 并面临一个问题，即在进行更新时，仅 162 行需要 20 秒。

• 预计执行计划

• 实际执行计划

Update FT SET
    ft.ValueName=avo.name
FROM #package FT 
inner join parts.Nop_PackageAttribute PA with(nolock)
    on PA.PackageID=ft.PackageID
        and PA.[Key]=FT.ZfeatureId 
inner join Nop_AcceptedValuesOption AVO with(nolock)
    ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
where FT.AcceptedValueID is not null

脚本示例

 create table #package
 (
 id int PRIMARY KEY IDENTITY(1,1),
 ZfeatureId INT NULL,
 AcceptedValueID INT NULL,
 PackageID INT NULL,
 ValueName NVARCHAR(2000) default ''
 )

我在#package 表上的索引

 create nonclustered index IDX_PackageID on #package(PackageID) include (ZfeatureId,AcceptedValueID , ValueName)
 create index acceptedvaluesidpackage_idx on #package(AcceptedValueID)

包属性表

ALTER TABLE [Parts].[Nop_PackageAttribute] ADD  CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_PackageAttribute_8_578153155__K2_K1_K3_4] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] ASC,
    [PackageAttributeID] ASC,
    [Key] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

CREATE NONCLUSTERED INDEX [IDX_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]


CREATE NONCLUSTERED INDEX [IDX_PakageID] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [IX_Nop_PackageAttribute_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] ASC
)
INCLUDE (   [PackageID],
    [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]


CREATE TABLE [Parts].[Nop_PackageAttribute](
    [PackageAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PackageID] [int] NOT NULL,
    [Key] [int] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
 CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer] TEXTIMAGE_ON [PRIMARY]

Nop_AcceptedValuesOption 表

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [_dta_index_Nop_AcceptedValuesOption_5_1669580986__col__] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID],
    [AcceptedValuesID],
    [Name],
    [DisplayOrder],
    [Description],
    [CreatedDate],
    [CreatedBy],
    [ModifiedDate],
    [ModifiedBy],
    [DeletedDate],
    [DeletedBy],
    [Is_Split],
    [AcceptedValuesOption_Value],
    [AcceptedValuesOption_Unit]
)WITH (DROP_EXISTING = OFF) ON [Customer]

CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID] ASC
)
INCLUDE (   [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K2_K4_1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] ASC,
    [DisplayOrder] ASC
)
INCLUDE (   [AcceptedValuesOptionID],
    [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160824-070515] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160829-015901] ON [dbo].[Nop_AcceptedValuesOption]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO

CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
    [AcceptedValuesOptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AcceptedValuesID] [int] NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [varchar](250) NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [Is_Split] [int] NULL,
    [AcceptedValuesOption_Value] [float] NULL,
    [AcceptedValuesOption_Unit] [nvarchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [DF_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] CHECK CONSTRAINT [FK_Nop_AcceptedValuesOption_Nop_AcceptedValues]
GO

那么，如何增强更新语句更快呢？

当我更新 20 行或没有行时，需要 11 分钟。

m.MaximumReflowTemperatureID <> r.z2valueid我的意思是两个表之间有20 行或没有行不同。

为什么我的更新如此缓慢，即使我更新了少量的行，甚至没有更新行？

如何处理？

我的实际执行计划：

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

语句更新耗时过长：

update r
set r.z2valueid=m.MaximumReflowTemperatureID
from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
    inner join z2datacore.parts.manufacturingdata m with(nolock)
        on m.partid=r.zpartid
    where m.MaximumReflowTemperatureID <> r.z2valueid

当通过用 select 替换 update 来尝试上述语句时，它需要相同的时间，11 分钟。

我需要更新的表[OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures]有 1400 万行，连接上的另一个表有 1500 万行。

示例表脚本：

CREATE TABLE [dbo].[ManufactureMaximumReflowTemperatures](
     [ID] [int] NOT NULL,
     [zpartid] [int] NULL,
     [key] [varchar](50) NULL,
     [value] [varchar](60) NULL,
     [Z2ValueID] [int] NULL,
     [csfeatureid] [int] NULL,
     [csvalueid] [int] NULL,
 PRIMARY KEY CLUSTERED 
 (
     [ID] 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   

 ALTER TABLE [dbo].[ManufactureMaximumReflowTemperatures] ADD PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenameandvalue_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] ASC,
     [Z2ValueID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenames_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemax_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [value] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemaxvalues_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [csvalueid] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 CREATE NONCLUSTERED INDEX [zpartid_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [zpartid] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 CREATE TABLE [Parts].[ManufacturingData](
     [LeadFinishId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartID] [int] NOT NULL,
     [LeadFinishMaterial] [varchar](50) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
     [MaximumReflowTemperatureID] [int] NULL,
        
  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 ) ON [Customer]
    
 GO
    
 SET ANSI_PADDING ON
 GO
 ALTER TABLE [Parts].[ManufacturingData] ADD  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

我在 sql server 2014 中工作，我需要在不使用自连接的情况下获取类别 c 和 x。

但是，我不知道该怎么做。

我的数据样本

create table #category
 (
 categoryc  int,
 categoryx int
 )
 insert into #category(categoryc,categoryx)
 values
 (19,20),
 (50,75),
 (80,70)
    
 create table #categorydetails
 (
 categoryid  int,
 categoryname nvarchar(300)
 )
 insert into #categorydetails(categoryid,categoryname)
 values
 (19,'bmw'),
 (20,'mercedees'),
 (50,'feat'),
 (75,'toyota'),
 (80,'mazda'),
 (70,'suzoky')
    
    
 select d1.categoryname as categoryc, d2.categoryname as categoryx
 from #category c
 left join #categorydetails d1 on d1.categoryid=c.categoryc
 left join #categorydetails d2 on d2.categoryid=c.categoryx

预期结果

我在 SQL Server 中遇到了一个问题，即非集群索引查找性能不佳。

下面是实际的执行计划 https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK

如何提高性能？

下面是表格定义

 CREATE TABLE [Parts].[ManufacturingData](
     [LeadFinishId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartID] [int] NOT NULL,
     [LeadFinishMaterial] [varchar](50) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
     [Revision_Id] [int] NULL,
     [BaseMaterialID] [int] NULL,
     [MSLID] [int] NULL,
     [MSLSource_Revision_id] [int] NULL,
     [MaximumReflowTemperatureID] [int] NULL,
     [ReflowTemperatureSource_Revision_Id] [int] NULL,
     [MaximumWaveTemperatureID] [int] NULL,
     [WaveTemperatureSource_Revision_ID] [int] NULL,
     [ReflowSolderTimeID] [int] NULL,
     [WaveSolderTimeID] [int] NULL,
     [NumberOfReflowCycleID] [int] NULL,
     [LeadFinishPlatingID] [int] NULL,
     [Comment] [varchar](100) NULL,
     [LeadfinishSourceTypeID] [int] NULL,
     [MSlSourceTypeID] [int] NULL,
     [ReflowTemperatureSourceTypeID] [int] NULL,
     [BasedOnID] [int] NULL,
     [LeadFreeProcessCapabilityID] [int] NULL,
     [BaseMaterialRevisionID] [int] NULL,
     [BaseMaterialSourceTypeID] [int] NULL,
     [UnderplatingRevisionID] [int] NULL,
     [UnderplatingSourceTypeID] [int] NULL,
     [ShelfLifeCondition] [int] NULL,
  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 ) ON [Customer]
    
 GO
    
 SET ANSI_PADDING ON
 GO

index seek used as below

 CREATE NONCLUSTERED INDEX [IDX_MSLID] ON [Parts].[ManufacturingData]
 (
     [MSLID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 GO

USE [Z2DataCore]
GO


 ALTER TABLE [Parts].[ManufacturingData] ADD  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 GO