PS: the inside of the single select statement is soon, is to merge into the speed is slow,
Is there a way to how to check the reason?
Procedure executed successfully
Time: 6.597 s
Specific statement is as follows:
ALTER procedure dbo. P_gys_book
@ xn varchar (9), @ xq int
As
The begin
Set nocount on
- declare @ xn varchar (9)='2019-2020, @ xq int=1;
The merge into dbo. Gys_book a
Using (select @ xn xn, @ xq xq, atul gawande ys, Anderson, SBN, a. CMC, a. CZZ, a.c bs, a. istprice, a.z k, a.c K_zsje, a.c K_total, a.c K_qty, b.R K_zsje, b.R K_total, b.R K_num rk_qty
The from
(select a.x n, a.x q, c.i SBN, SAN Antonio CMC, SAN Antonio CZZ, Arthur c. bs, c.g ys, d.l istprice, d.z k, the sum (b.q ty) as CK_qty, round (sum (d.l istprice * * b.q ty d.z k), 2) CK_zsje, sum (d.l b.q istprice * ty) CK_total
The from dbo. OrderHeader a INNER JOIN dbo. Named OrderDetail b ON Anderson, d=b.o rderid
INNER JOIN dbo. Jcjbxxb on c b.b ookid=c.i d
Inner join dbo. On c.i Jcjbxxb_ListPriceHistory d d=db ookid and a.x n=d.x n and a.x q=d.x q
Where a.x n=@ xn and a.x q=@ xq
Group by a.x n, a.x q, c.i SBN, SAN Antonio CMC, SAN Antonio CZZ, Arthur c. bs, c.g ys, d.l istprice, d.z k
) a
Left the join
(select xn, xq, isbn, gys, sum (num) as RK_num, isnull (sum (total), 0) as RK_total, sum (zsje) as RK_zsje from guest. JCRKB where xn=@ xn and xq=@ xq GROUP BY xn, xq, isbn, gys
) as ON a.x n=b.x n and b=b.x a.x q q and Anderson, SBN=b.i SBN and al-qeada ys=b.g ys) ON b (n=b.x a.x n and a.x q=b.x q and al-qeada ys=b.g ys and Anderson, SBN=b.i SBN)
The when matched then
The update
The set a.c k_zsje=biggest k_zsje, a.c k_total=biggest k_total, a.c k_qty=biggest k_qty, a.r k_zsje=b.r k_zsje, a.r k_total=b.r k_total, a.r k_qty=b.r k_qty, Amy polumbo rice=b.l istprice, a.c bs=biggest bs, a. CMC=b.j CMC, a. CZZ=b.j CZZ
When not matched then
Insert (xn, xq, gys, isbn, JCMC, JCZZ, CBS, price, zk, ck_zsje, ck_total, ck_qty, rk_zsje, rk_total, rk_qty)
Values (b.x b.x n, q, b.g ys, b.i SBN, b.j CMC, b.j CZZ, biggest bs, b.l istprice, b.z k, biggest k_zsje, biggest k_total, biggest k_qty, isnull (b.r k_zsje, 0), isnull (b.r k_total, 0), isnull (b.r k_qty, 0));
- statistics influence the number of rows
Select @ @ rowcount;
end;