| How to reduce time for below SQL. [message #670886] |
Wed, 01 August 2018 00:06  |
 |
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 #670893 is a reply to message #670891] |
Wed, 01 August 2018 02:53  |
gazzag
Messages: 1119
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
