Nested in the case when the select, slow, how to optimize it, thank you!-CodePudding

This code from code is slow [select tabs1. EligibleQty
The from tabs tabs1
Where tabs1. Rn=tabs2. Rn 1
And tabs1. MO=tabs2. MO], his role was to take a number of qualified data before - complete the current row number=wip

 
With tabs as (
The select b.M O, 
CompleteQty, 
B.O perationNum, 
B.O pDescription, 
EligibleQty, 
ScrapQty, 
ROW_NUMBER () over (partition by b.M O order by OperationNum asc) rn 
The from (select b.M O, 
The sum (pleteQty b.Com as CompleteQty, 
Moo. OperationNum, 
R.O pDescription, 
The sum (b.E ligibleQty) as EligibleQty, 
The sum (b.S crapQty) as ScrapQty 
The from MO_DispatchCompleteOrder a 
Left the join MO_DispatchCompleteOrderDocLine b on Anderson, D=
B.D ispatchCompleteOrderDoc 
Left the join MO_MOOperation moo on b.M OOperation=moo. ID 
Left the join MO_MOOperation_Trl r on moo. ID=r.I D 
Where moo. IsCountPoint=1 
And a. d. ocState=2 
Group by b.M O, moo. OperationNum r.O pDescription) b) 
The select mo. ID, 
O. chua ode as OrgCode - organization 
Mo. DocNo, -- -- 
 documents,The item Code as ItemMasterCode, - material goods 
Mo. StartDate, -- -- starting time 
X.S equence - working procedure code 
R1. The Description, -- -- process Description 
Mo. ProductQty, -- -- production quantity 
ISNULL (routing.Com pleteQty, 0) CompleteQty - number of completed 
ISNULL (routing. EligibleQty, 0) EligibleQty, - the number of qualified 
ISNULL (routing. ScrapQty, 0) ScrapQty, - scrap amount 
ISNULL (routing. WorkQty, 0) WorkQty -- work order wip 
The from MO_MO mo 
Left the join CBO_ItemMaster item on mo. ItemMaster=item. ID 
Left the join Base_Organization o on mo.Org=o.I D 
Left the join CBO_Routing r on mo. Routing=r.I D 
And r.Org=mo.Org 
Left the join CBO_Operation x on r.I D=x.R outing 
And x.Org=mo.Org 
Left the join CBO_Operation_Trl r1 on x.I D=r1. ID 
Left the join (
The select tabs2. *, 
Case 
When an rn=1 then 
0 
The else 
(the select tabs1 EligibleQty 
The from tabs tabs1 
Where tabs1. Rn=tabs2. Rn 1 
And tabs1. MO=tabs2. MO) - tabs2.Com pleteQty 
End as WorkQty 
The from tabs tabs2 
) routing 
On mo. ID=routing. Mo and x.S equence=routing. OperationNum 
Where x.I sCountPoint=1 and (routing. Rn & lt;> 1 or routing. An rn is null) 
The order by mo. DocNo desc, x.S equence asc

CodePudding user response:

 
Sql20012 + words 
Lag () function know about the

CodePudding user response:

Try using LAG or LEAD function

CodePudding user response:

Why don't you try to use temporary table way, each a Select * from table blocks the execution of the data in the temporary table, in the following query temporary table directly, and use it every time than you this way the efficiency of the fast now!