Click here to Skip to main content
15,614,683 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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

SQL
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 
Posted
Comments
CHill60 7-Oct-22 12:16pm    
I cannot view your expected result because that location is blocked on my network. I'm pretty sure you've been told before to include all the details here - there is no need for pictures
ahmed_sa 7-Oct-22 12:22pm    
i need to write another query instead of above because comparison make query is very slow with big count so can you help me with another query writing
CHill60 9-Oct-22 19:05pm    
Probably.
But you have to supply all the related information here.
And you need to actually apply all the advice you've been given on all your other questions first.
Indices. Indices. Indices.
0x01AA 7-Oct-22 16:29pm    
Simply missing some evident indices? This, because your query looks pretty simple...
Keep in mind MS SQL does not create an index for a foreign key relation, you have to define it (that index) explicitly.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900