CODEWITHSUNDEEP
sqloracle-database
aliceau
aliceau

Reputation: 5

sql fetching time slower speed up the query

SELECT 
SA.YEAR,            
sum(SA.QUANTITY1),                          
sum(SA.QUANTITY2),          
sum(SA.QUANTITY3),   
sum(SA.QUANTITY4),  
sum(CD.QUANTITY5),   
sum(CD.QUANTITY6),
sum(CD.QUANTITY7),  
sum(CD.QUANTITY8)

FROM SIEM_DETAILS SA, REAP_DETAILS CD

GROUP BY SA.YEAR;

I have an sql query where it displays sum of the QUANTITYs which were grouped by the year. The issue is it takes up around 25 minutes to fetch the rows.

Those tables contain around 20,000 records.

I tried to create an index on each of the column but it did not work.

How can I speed up the query where it can fetch the records and display the sum in a very short amount of time???

Below is a sample SQL Fiddle

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e220908a49c0dc8ac61388aa1b241ea4

Tried this way too

FROM SIEM_DETAILS SA
JOIN REAP_DETAILS CD
ON CD.ENTRY_ID = SA.ENTRY_ID

GROUP BY SA.YEAR;

But the time to display result is slow

Upvotes: 0

Views: 362

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Shouldn't it contain a join condition there? Like:

SELECT 
SA.YEAR,            
sum(SA.QUANTITY1),                          
sum(SA.QUANTITY2),          
sum(SA.QUANTITY3),   
sum(SA.QUANTITY4),  
sum(CD.QUANTITY5),   
sum(CD.QUANTITY6),
sum(CD.QUANTITY7),  
sum(CD.QUANTITY8)

FROM SIEM_DETAILS SA, REAP_DETAILS CD
WHERE sa.ENTRY_ID=cd.ENTRY_ID
GROUP BY SA.YEAR;

Update: There is also another approach: to use manual group-by-placement transformation:

SELECT 
   SA.YEAR,            
   sum(SA.QUANTITY1),                          
   sum(SA.QUANTITY2),          
   sum(SA.QUANTITY3),   
   sum(SA.QUANTITY4),  
   sum(CD.QUANTITY5),   
   sum(CD.QUANTITY6),
   sum(CD.QUANTITY7),  
   sum(CD.QUANTITY8)
FROM 
(
   select 
      YEAR,
      ENTRY_ID,
      sum(QUANTITY1) QUANTITY1,                          
      sum(QUANTITY2) QUANTITY2,          
      sum(QUANTITY3) QUANTITY3,   
      sum(QUANTITY4) QUANTITY4
   FROM SIEM_DETAILS
   group by YEAR,ENTRY_ID
) sa,
(
   select
      ENTRY_ID
      sum(QUANTITY5) QUANTITY5,   
      sum(QUANTITY6) QUANTITY6,
      sum(QUANTITY7) QUANTITY7,  
      sum(QUANTITY8) QUANTITY8
   from REAP_DETAILS 
   group by ENTRY_ID
) cd
WHERE sa.ENTRY_ID=cd.ENTRY_ID
GROUP BY SA.YEAR;

I'm not sure if your REAP_DETAILS has YEAR column. If yes, you need to add this column into cd inline view like:

SELECT 
   SA.YEAR,            
   sum(SA.QUANTITY1),                          
   sum(SA.QUANTITY2),          
   sum(SA.QUANTITY3),   
   sum(SA.QUANTITY4),  
   sum(CD.QUANTITY5),   
   sum(CD.QUANTITY6),
   sum(CD.QUANTITY7),  
   sum(CD.QUANTITY8)
FROM 
(
   select 
      YEAR,
      ENTRY_ID,
      sum(QUANTITY1) QUANTITY1,                          
      sum(QUANTITY2) QUANTITY2,          
      sum(QUANTITY3) QUANTITY3,   
      sum(QUANTITY4) QUANTITY4
   FROM SIEM_DETAILS
   group by YEAR,ENTRY_ID
) sa,
(
   select
      YEAR,ENTRY_ID
      sum(QUANTITY5) QUANTITY5,   
      sum(QUANTITY6) QUANTITY6,
      sum(QUANTITY7) QUANTITY7,  
      sum(QUANTITY8) QUANTITY8
   from REAP_DETAILS 
   group by YEAR,ENTRY_ID
) cd
WHERE sa.ENTRY_ID=cd.ENTRY_ID
GROUP BY SA.YEAR;

Upvotes: 1

Related Questions