2

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
0

2 Answers 2

1

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 which happen before the SELECT TOP. The GROUP BY may have to touch thousands of rows before producing a reduced result set. For that reason, it's not as if you're passing 5 rows to the GROUP BY and expecting it to take action on them.

Also, you only provided an estimated plan. It estimates that 522 rows will be read. But without an actual execution plan, there is no way to know how many rows were actually read.

The estimated plan also has a Key Lookup because you do not have an index covering all of the columns being referenced in your query. Key Lookups are typically bad, because they execute once for each row. In this case, it's estimated your Index Scan will return 522 rows. For each of those 522 rows, there would be a distinct Key Lookup. Reviewing your current indexes, I would considering adding this index to hopefully get you an Index Seek without a Key Lookup.

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

Additionally, after this you should be able to drop this index.

DROP INDEX IDX_SourcingNotMappedParts_VCompanyId]
0
-1

You did not create the proper index, it seems. But before creating them investigate the query plan of your query. If your using SSMS, then select the query and press Ctrl + L to enable the query plan. Missing indexes are shown in query plan too.

This is a query created by Pinal Dave. It shows the top 25 indexes you may missed to create.

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.