MySQL Forums
Forum List  »  Newbie

optimalize ugly view
Posted by: Tomas Gorda
Date: May 25, 2020 08:37AM

Hello,

i had to made some ugly view and i'd like to ask, how can be optimalized ? I tried to use left joins, bud processing takes more time then ugly one :)

Here is the first version:

Select * from
(SELECT k.id, k.stroj, k.order1 as ProdOrder, k.partnumber1 as PartNr, k.ST1_value1 as ST1,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_sap,
k.ST2_value1 as ST2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_sap,
k.ST3_value1 as ST3,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_sap,
k.ST4_value1 as ST4,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_sap,
k.ST5_value1 as ST5,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_sap,
k.ST6_value1 as ST6,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_sap,
k.ST7_value1 as ST7,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_sap,
k.ST8_value1 as ST8,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_sap,
k.ST9_value1 as ST9,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_sap,
k.ST10_value1 as ST10,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_sap,
k.NOKcelkem_value1 as NOKcelkem, k.OKcelkem_value1 as OKcelkem, smena, timeStamp
from kanban k
INNER JOIN operace_view ov on ov.stroj=k.stroj) data1 group by data1.id
union all
Select * from(SELECT k.id, k.stroj, k.order2, k.partnumber2, k.ST1_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_sap,
k.ST2_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_sap,
k.ST3_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_sap,
k.ST4_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_sap,
k.ST5_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_sap,
k.ST6_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_sap,
k.ST7_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_sap,
k.ST8_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_sap,
k.ST9_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_sap,
k.ST10_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_sap,
k.NOKcelkem_value2, k.OKcelkem_value2, smena, timeStamp
from kanban k
INNER JOIN operace_view ov on ov.stroj=k.stroj) data2 group by data2.id

Options: ReplyQuote


Subject
Written By
Posted
optimalize ugly view
May 25, 2020 08:37AM
May 25, 2020 08:38AM
May 25, 2020 11:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.