when make select to top 5 of rows from table it take too much time?



  • I work on sql server 2019 i face issue when make select top 5 rows it take too much time .

    Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows .

    when run statment select top 5 it take too much time exceed 15 minutes .

    so how to make it faster

    select statment that have issue

    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
    

    my estimated execution plan

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

    Notes : I try to make select columns above without using select into but still very slow .

    sample table scripts and indexes

    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



  • Your issue doesn’t really have anything directly to do to with the TOP 5. You have a GROUP BY and HAVING clause. Both of those happen before your SELECT TOP, and might touch thousands of rows to produce their results, before you even choose the top 5 from those results. You only provided an estimated plan, so there’s no way to know how many rows are being read.

    The estimated plan has a Key Lookup because you don’t not have an index covering all of the columns being referenced in your query.

    Looking at your indexes, consider adding this index to avoid the key lookups…

    CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts]
    (
        [VCompanyId],
        [sourcetypeid],
        [PriorityLevel]
    )
    INCLUDE (
        [GivenPartNumber_Non],
        [sourcetypeid]
    )
    

    Drop this index

    DROP INDEX IDX_SourcingNotMappedParts_VCompanyId]
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2