select * from
(select t.id,
t.num,
t.declaration_Date,
ct.type,
g.name,
g.tnved_code,
g.g31_amount,
g.brutto,
g.netto,
case
when t.state = 1 then
1
else
0
end
env,
CASE
WHEN ct.type = 1 THEN
t.g2_code2
ELSE
'200794867'
END as seller_tin,
CASE
WHEN ct.type = 1 THEN
'200794867'
ELSE
t.g8_code2
END as buyer_tin,
CASE
WHEN ct.type = 1 THEN
t.g2_name
ELSE
t.g8_name
END as seller_name,
CASE
WHEN ct.type = 2 THEN
t.g8_name
ELSE
t.g2_name
END as buyer_name,
sum(g.cost_facture * t.curr_Course) as cost_Facture,
SUM(g1.good_payment_27 g2.good_pay_late_27) as good_payment_27,
SUM(g1.good_payment_29 g2.good_pay_late_29) as good_payment_29
from valyuta_declarations t,
valyuta_CONTRACT_SUB_TYPES ct,
valyuta_GOODS g,
(select g.id,
g.declaration_id,
g.cost_facture,
SUM(CASE
WHEN gp.payment_code = 27 THEN
gp.payment_amount
ELSE
0
END) as good_payment_27,
SUM(CASE
WHEN gp.payment_code = 29 THEN
gp.payment_amount
ELSE
0
END) as good_payment_29
from valyuta_goods g,
valyuta_good_payments gp
where g.id = gp.good_id( )
group by g.id, g.declaration_id, g.cost_facture) g1,
(select g.id,
g.declaration_id,
g.cost_facture,
SUM(CASE
WHEN gpl.L_G47_TYPE = 27 THEN
gpl.L_G47_SUM
ELSE
0
END) as good_pay_late_27,
SUM(CASE
WHEN gpl.L_G47_TYPE = 29 THEN
gpl.L_G47_SUM
ELSE
0
END) as good_pay_late_29
from valyuta_goods g,
valyuta_GOOD_PAY_LATE gpl
where g.id = gpl.good_id( )
group by g.id, g.declaration_id, g.cost_facture) g2
where t.type = ct.short_name
and t.id = g.DECLARATION_ID
and g.id = g1.id
and g.id = g2.id
and ((ct.type = 1 and t.g1_b in (10, 11)) or (ct.type = 2 and t.g1_b = 40))
GROUP BY t.id,
t.num,
t.declaration_Date,
t.state,
ct.type,
g.name,
g.tnved_code,
g.g31_amount,
g.brutto,
g.netto,
t.G2_CODE2,
t.G2_NAME,
t.g8_code2,
t.g8_name) a
where a.type = 1 and a.env = 1 and a.declaration_date between to_date('01-01-2022','dd-mm-yyyy') and to_date('31-03-2022','dd-mm-yyyy')
and seller_tin in ('302857893')
There is my code. It takes 30 minutes to see result. How can i optimize that? I have no idea how to optimize that. KALEFMEAKMF:ELSMF"LEAMFLE:AMF:EKSAMFL:EAMF{PEWPEF4wefasfa sdasdasdas
asd as da sd as d
ASDASDAS DAS DAS D AS DAS D AS D AS DAS D AS D AS D ASD AS D ASD AS D dAKMDASKDAS:DLM AL: MASD: LMAS: LMSA:L MDAS:LMD :LASM D:LASM D:LASMD :LASM D:ASLMD AS:LMD :ASLM
CodePudding user response：
To improve on the performance of that query, you'll want to have a look at the execution plan first. Most SQL server editions offer hints to create indexes. Creating those will already result in a considerable speed-up, in particular in the subqueries. The numbers on the execution plan items will tell you where the critical path is: rearranging parts of the query, or changing the location where expensive joins and comparisons are made, will speed things up.
The valuyuta... group of tables needs to use the proper join syntax (INNER JOIN). The fragments and g.id = g1.id and and g.id = g2.id can then be removed from the WHERE clause and go to the join criteria of their respective table.
Furthermore, techniques exist to get rid of the subqueries altogether, for example pre-computing as a CTE, or a materialized view.
CodePudding user response：
There are plenty of conditions and joined tables before getting the date filter from table valyuta_declarations. Please filter this table before joining the other tables. Try below updated query.
select * from
(select t.id,
t.num,
t.declaration_Date,
ct.type,
g.name,
g.tnved_code,
g.g31_amount,
g.brutto,
g.netto,
case
when t.state = 1 then
1
else
0
end
env,
CASE
WHEN ct.type = 1 THEN
t.g2_code2
ELSE
'200794867'
END as seller_tin,
CASE
WHEN ct.type = 1 THEN
'200794867'
ELSE
t.g8_code2
END as buyer_tin,
CASE
WHEN ct.type = 1 THEN
t.g2_name
ELSE
t.g8_name
END as seller_name,
CASE
WHEN ct.type = 2 THEN
t.g8_name
ELSE
t.g2_name
END as buyer_name,
sum(g.cost_facture * t.curr_Course) as cost_Facture,
SUM(g1.good_payment_27 g2.good_pay_late_27) as good_payment_27,
SUM(g1.good_payment_29 g2.good_pay_late_29) as good_payment_29
from
(select id, num, declaration_Date, type, g1_b
from valyuta_declarations
where declaration_date between to_date('01-01-2022','dd-mm-yyyy') and to_date('31-03-2022','dd-mm-yyyy')) t,
inner join valyuta_CONTRACT_SUB_TYPES ct on t.type = ct.short_name
inner join valyuta_GOODS g on t.id = g.DECLARATION_ID
inner join (select g.id,
g.declaration_id,
g.cost_facture,
SUM(CASE
WHEN gp.payment_code = 27 THEN
gp.payment_amount
ELSE
0
END) as good_payment_27,
SUM(CASE
WHEN gp.payment_code = 29 THEN
gp.payment_amount
ELSE
0
END) as good_payment_29
from valyuta_goods g,
valyuta_good_payments gp
where g.id = gp.good_id( )
group by g.id, g.declaration_id, g.cost_facture) g1 on g.id = g1.id
inner join(select g.id,
g.declaration_id,
g.cost_facture,
SUM(CASE
WHEN gpl.L_G47_TYPE = 27 THEN
gpl.L_G47_SUM
ELSE
0
END) as good_pay_late_27,
SUM(CASE
WHEN gpl.L_G47_TYPE = 29 THEN
gpl.L_G47_SUM
ELSE
0
END) as good_pay_late_29
from valyuta_goods g,
valyuta_GOOD_PAY_LATE gpl
where g.id = gpl.good_id( )
group by g.id, g.declaration_id, g.cost_facture) g2 on g.id = g2.id
where
((ct.type = 1 and t.g1_b in (10, 11)) or (ct.type = 2 and t.g1_b = 40))
GROUP BY t.id,
t.num,
t.declaration_Date,
t.state,
ct.type,
g.name,
g.tnved_code,
g.g31_amount,
g.brutto,
g.netto,
t.G2_CODE2,
t.G2_NAME,
t.g8_code2,
t.g8_name) a
where a.type = 1 and a.env = 1
and seller_tin in ('302857893')