Home > database >  A great god can you help me to optimize my statement, I need this statement query out 5 minutes or s
A great god can you help me to optimize my statement, I need this statement query out 5 minutes or s

Time:10-13

SELECT 'payment part as CLS,
STLCHK stlchkno,
STLCHK mkedat,
(select empdes from emp where empid=STLCHK. MKR) as MKR,
Gdsbuy srcbllno,
Round (sum (gdsbuy. Amt), 2) the as bivcdtl_amt,
Round (sum (gdsbuy. Patamt), 2) the as bivcdtl_patamt,
(select distinct '[' + rtrim (BLL. Bllid) +'] '+ blldes from the BLL the where the BLL. Bllid=gdsbuy. Bllid) as blldes,
Gdsbuy ctno,
(select openaccbnkr from PRV where prvid=STLCHK. Prvid) as the op,
(select actno from PRV where prvid=STLCHK. Prvid) as actno,
(select BNK from PRV where prvid=STLCHK. Prvid) as abnk,
(select dptid from DPT where dptid in (select savdptid from stkin where stkinno=gdsbuy. Srcbllno)) as fd,
(select the '[' + rtrim (prvid) +'] '+ prvdes from PRV where prvid=STLCHK. Prvid) as prvdes
The FROM ((STLCHK inner join STLBLL on STLCHK. Stlchkno=STLBLL. Stlchkno) inner join bivc on bivc. Bivcno=STLBLL. Lnkstlno) inner join gdsbuy on bivc. Bivcno=gdsbuy. Lnkivcno
WHERE STLCHK. Prvid like: as_prvid and
STLCHK. Mkedat>=: as_bdate and STLCHK. Mkedat<=: as_edate and STLCHK. MKR in (select empid from emp where dptid & lt;> '311') and
Gdsbuy. BSTLFLG='y'
GROUP BY STLCHK stlchkno,
STLCHK prvid,
STLCHK mkedat,
STLCHK MKR,
Gdsbuy bllid,
Gdsbuy srcbllno,
Gdsbuy. Ctno
The union
SELECT 'fees' as CLS,
STLCHK stlchkno,
STLCHK mkedat,
(select empdes from emp where empid=STLCHK. MKR) as MKR,
"' as srcbllno,
0 as bivcdtl_amt,
The sum (STLCHKDTL. Feeamt) as bivcdtl_patamt,
(select the '[' + rtrim (feeitm feeitmid) +'] '+ feeitm. Feeitmdes from feeitm where feeitm. Feeitmid=STLCHKDTL. Feeitmid) as blldes,
"' as ctno,
"' as the op,
"' as actno,
"' as abnk,
"' as fd,
"' as prvdes
The FROM STLCHK,
STLCHKDTL
WHERE STLCHK. Prvid like: as_prvid and STLCHKDTL stlchkno=STLCHK. Stlchkno and
STLCHK. Mkedat>=: as_bdate and STLCHK. Mkedat<=: as_edate and STLCHK. MKR in (select empid from emp where dptid & lt;> '311')
Group by STLCHK stlchkno,
STLCHK mkedat,
STLCHK MKR,
STLCHKDTL. Feeitmid
I really do not have what points... Thank you god!

CodePudding user response:

With the exists in the union with union all

CodePudding user response:

Thank you, I'll try for a while

CodePudding user response:

Complicated
Haha


CodePudding user response:

The
reference 3 floor tomok response:

of complicatedHaha
is also need table structure

CodePudding user response:

reference 1st floor seven125 response:
change exists in the union with union all
~ more slowly

CodePudding user response:

First to deal with STLCHK table conditions then associated with other tables for
And then reduce the subquery use Left join instead

CodePudding user response:

For the where condition in field, the index