Home » Open Source » MySQL » How to reduce time for below SQL. (Mysql Workbench 6.3)
How to reduce time for below SQL. [message #670886] Wed, 01 August 2018 00:06 Go to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member

This is SQL for fetching rating for each supplier. Please find the number of records for each table.

select count(*) from SUPPLIER;
18,48,338

select count(*) from ECOVADIS_RATING;
2,07,277

select count(*) from WCA_RATING;
1521

select count(*) from CSR_INTERNAL_AUDIT;
224


select ecovadisScore, csrScore, smetaScore, s.PROCUBE_SEQ_ID from SUPPLIER s
join (
select er.GLOBAL_RATING as ecovadisScore, er.SUPPLIER_FK as ecovadisSupplier from ECOVADIS_RATING er
inner join SUPPLIER s on er.SUPPLIER_FK = s.PROCUBE_SEQ_ID
where er.ECOVADIS_ID = (select MAX(e.ECOVADIS_ID) from ECOVADIS_RATING e where e.SUPPLIER_FK = s.PROCUBE_SEQ_ID)) ecovadis
on s.PROCUBE_SEQ_ID = ecovadisSupplier
join (
select csr.GLOBAL_RATING AS csrScore, csr.SUPPLIER_FK as csrSupplier from CSR_INTERNAL_AUDIT csr
inner join SUPPLIER s on csr.SUPPLIER_FK = s.PROCUBE_SEQ_ID
where csr.CSR_ID = (select MAX(csr1.CSR_ID) from CSR_INTERNAL_AUDIT csr1 where csr1.SUPPLIER_FK = s.PROCUBE_SEQ_ID)) csr
on s.PROCUBE_SEQ_ID = csrSupplier
join (
select wca.SMETA_RATING as smetaScore, wca.SUPPLIER_FK as wcaSupplier from WCA_RATING wca
inner join SUPPLIER s on wca.SUPPLIER_FK = s.PROCUBE_SEQ_ID
where wca.WCA_ID = (select MAX(wca1.WCA_ID) from WCA_RATING wca1 where wca1.SUPPLIER_FK = s.PROCUBE_SEQ_ID)) smeta
on s.PROCUBE_SEQ_ID = wcaSupplier;
Re: How to reduce time for below SQL. [message #670888 is a reply to message #670886] Wed, 01 August 2018 01:25 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
i have tried this way too.

SELECT SP.PROCUBE_SEQ_ID, WCA.SMETA_RATING, ER.GLOBAL_RATING, CSR.GLOBAL_RATING FROM SUPPLIER SP
LEFT OUTER JOIN ECOVADIS_RATING ER ON SP.PROCUBE_SEQ_ID = ER.SUPPLIER_FK and
ER.ECOVADIS_ID = (select MAX(ECOVADIS_ID) from ECOVADIS_RATING where SUPPLIER_FK = SP.PROCUBE_SEQ_ID)
LEFT OUTER JOIN WCA_RATING WCA ON SP.PROCUBE_SEQ_ID = WCA.SUPPLIER_FK
AND WCA.WCA_ID = (select MAX(WCA_ID) from WCA_RATING where SUPPLIER_FK = SP.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CSR_INTERNAL_AUDIT CSR ON SP.PROCUBE_SEQ_ID = CSR.SUPPLIER_FK
AND CSR.CSR_ID = (select MAX(CSR_ID) from CSR_INTERNAL_AUDIT where SUPPLIER_FK = SP.PROCUBE_SEQ_ID)
Re: How to reduce time for below SQL. [message #670889 is a reply to message #670888] Wed, 01 August 2018 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
Format your query, if you don't know how to do it, learn it using SQL Formatter.

Re: How to reduce time for below SQL. [message #670890 is a reply to message #670888] Wed, 01 August 2018 02:07 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Moved to the MySQL forum.
Re: How to reduce time for below SQL. [message #670891 is a reply to message #670890] Wed, 01 August 2018 02:24 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
SELECT SP.procube_seq_id,
WCA.smeta_rating,
ER.global_rating,
CSR.global_rating
FROM supplier SP
LEFT OUTER JOIN ecovadis_rating ER
ON SP.procube_seq_id = ER.supplier_fk
AND ER.ecovadis_id = (SELECT Max(ecovadis_id)
FROM ecovadis_rating
WHERE
supplier_fk = SP.procube_seq_id)
LEFT OUTER JOIN wca_rating WCA
ON SP.procube_seq_id = WCA.supplier_fk
AND WCA.wca_id = (SELECT Max(wca_id)
FROM wca_rating
WHERE supplier_fk = SP.procube_seq_id)
LEFT OUTER JOIN csr_internal_audit CSR
ON SP.procube_seq_id = CSR.supplier_fk
AND CSR.csr_id = (SELECT Max(csr_id)
FROM csr_internal_audit
WHERE supplier_fk = SP.procube_seq_id)
Re: How to reduce time for below SQL. [message #670892 is a reply to message #670891] Wed, 01 August 2018 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 01 August 2018 09:05

Please read How to use [code] tags and make your code easier to read.
Format your query, if you don't know how to do it, learn it using SQL Formatter.
Re: How to reduce time for below SQL. [message #670893 is a reply to message #670891] Wed, 01 August 2018 02:53 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Formatted code looks like this and is easier for us to read and therefore help you:
SELECT SP.procube_seq_id,
       WCA.smeta_rating,
       ER.global_rating,
       CSR.global_rating
  FROM supplier SP
       LEFT OUTER JOIN ecovadis_rating ER
          ON     SP.procube_seq_id = ER.supplier_fk
             AND ER.ecovadis_id = (SELECT MAX (ecovadis_id)
                                     FROM ecovadis_rating
                                    WHERE supplier_fk = SP.procube_seq_id)
       LEFT OUTER JOIN wca_rating WCA
          ON     SP.procube_seq_id = WCA.supplier_fk
             AND WCA.wca_id = (SELECT MAX (wca_id)
                                 FROM wca_rating
                                WHERE supplier_fk = SP.procube_seq_id)
       LEFT OUTER JOIN csr_internal_audit CSR
          ON     SP.procube_seq_id = CSR.supplier_fk
             AND CSR.csr_id = (SELECT MAX (csr_id)
                                 FROM csr_internal_audit
                                WHERE supplier_fk = SP.procube_seq_id)

[edit: typo]

[Updated on: Wed, 01 August 2018 03:02]

Report message to a moderator

Re: How to reduce time for below SQL. [message #670894 is a reply to message #670893] Wed, 01 August 2018 03:03 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
thanks, I have done by given link. Thanks for your support. Please let me know, if you have anything is mind for SQL time reducing.
Re: How to reduce time for below SQL. [message #670895 is a reply to message #670891] Wed, 01 August 2018 04:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Is this statement in the slow query log? And you need to EXPLAIN it.
Re: How to reduce time for below SQL. [message #670896 is a reply to message #670895] Wed, 01 August 2018 04:46 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
ok..thanks for your help. this is my final SQL.

SELECT DISTINCT S.procube_seq_id, 
                WR.smeta_rating, 
                ER.global_rating, 
                CSR.global_rating 
FROM   supplier S 
       LEFT OUTER JOIN ecovadis_rating ER 
                    ON S.procube_seq_id = ER.supplier_fk 
                       AND ER.ecovadis_id = (SELECT Max(ecovadis_id) 
                                             FROM   ecovadis_rating 
                                             WHERE 
                           supplier_fk = S.procube_seq_id) 
       LEFT OUTER JOIN wca_rating WR 
                    ON S.procube_seq_id = WR.supplier_fk 
                       AND WR.wca_id = (SELECT Max(wca_id) 
                                        FROM   wca_rating 
                                        WHERE  supplier_fk = S.procube_seq_id) 
       LEFT OUTER JOIN csr_internal_audit CSR 
                    ON S.procube_seq_id = CSR.supplier_fk 
                       AND CSR.csr_id = (SELECT Max(csr_id) 
                                         FROM   csr_internal_audit 
                                         WHERE  supplier_fk = S.procube_seq_id) 
WHERE  ER.ecovadis_id IS NOT NULL 
        OR WR.wca_id IS NOT NULL 
        OR CSR.csr_id IS NOT NULL 
Re: How to reduce time for below SQL. [message #670897 is a reply to message #670896] Wed, 01 August 2018 05:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Do you understand what is meant using EXPLAIN on a query? Do you know what the slow query log is?
Re: How to reduce time for below SQL. [message #670898 is a reply to message #670896] Wed, 01 August 2018 05:09 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Using MySQL EXPLAIN.
Re: How to reduce time for below SQL. [message #670899 is a reply to message #670898] Wed, 01 August 2018 05:23 Go to previous message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
I know about it MySQL EXPLAIN. Now my query is not giving lost connection in MYSQL workbench and providing result successfully.
Previous Topic: MySQL Issue
Next Topic: Insert data from Oracle 11g to MYSQL
Goto Forum:
  


Current Time: Thu Mar 28 15:12:46 CDT 2024