I work on sql server 2019 i face issue on slow running
when I have 100000 part on table PartsHaveBestDisplayOrder and all these parts have same package and same coderulesid and Different display order
so it take too much time
so are there are any way to achieve that quickly
script code sql server
IF OBJECT_ID(N'Extractreports.dbo.PartsHaveBestDisplayOrder') Is NOT NUll
BEGIN
DROP TABLE Extractreports.dbo.PartsHaveBestDisplayOrder
END
create table Extractreports.dbo.PartsHaveBestDisplayOrder
(
PartId int,
CodeRulesId int,
PackageId int,
DisplayOrder int
)
insert into Extractreports.dbo.PartsHaveBestDisplayOrder(PartId,CodeRulesId,PackageId,DisplayOrder)
values
(12111,120,19110,1),
(12901,120,19110,5),
(33091,120,19110,4),
(30911,120,19110,3),
(55899,120,19110,2),
(80122,120,19110,1),
(30918,120,19110,3),
(76012,120,19110,2),
(54031,120,19110,4),
(30401,120,19110,5)
expected result
File sharing and storage made simple[
^]
What I have tried:
what i try as below :
select T1.PartID as OrignalPartId , T2.PartId as RecomendationPartId,T1.DisplayOrder as OriginalDisplayOrder,T2.DisplayOrder as RecomendedDisplayOrder
from Extractreports.dbo.PartsHaveBestDisplayOrder T1 inner join
Extractreports.dbo.PartsHaveBestDisplayOrder T2 on T1.CodeRulesId =T2.CodeRulesId and T1.PackageID=t2.PackageID
where T2.DisplayOrder >t1.DisplayOrder