How to display original part and recomended part with each other but must recomended Part have bigger DisplayOrder?

ahmed salah 3,216 Reputation points
2022-10-06T16:29:15.627+00:00

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)

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

248202-image.png

so please can you help me by another way to achive that quickly

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,211 Reputation points
    2022-10-06T17:02:32.64+00:00

    Assuming you have no constraints or indexes on the table like in your question, this clustered index should improve performance: 

    CREATE UNIQUE CLUSTERED INDEX cdx ON dbo.PartsHaveBestDisplayOrder  (CodeRulesId,PackageId,DisplayOrder,PartId);
  2. NikoXu-msft 1,911 Reputation points
    2022-10-07T08:00:32.69+00:00

    Hi @ahmed salah ,

    Creating indexes is one of the more effective ways to improve query performance, so if you want to use SQL Graph, then check out this article:
    https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview?view=sql-server-ver16

    Best regards
    Niko

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".