CODEWITHSUNDEEP
sql
magickk
magickk

Reputation: 19

Sql query tuning/optimization

For large amounts of data, it is taking a lot of time to execute.

Please help tune this query. 

select * 
from 
    (select cs.sch, cs.cls, cs.std, d.date, d.count
     from 
         (select c.sch, c.cls, s.std
          from 
              (select distinct sch, cls from Data) c  --List of school/classes
          cross join 
              (select distinct std from Data) s --list of std
         ) cs  --every possible combination of school/classes and std
     left outer join 
         Data D on D.sch = cs.sch and D.cls = cs.cls and D.std = cs.std  --try and join to the original data
     group by 
         c.sch, c.cls, s.std, d.date, d.count) 
order by 
    cs.sch, cs.cls, 
    case 
       when (cs.std= 'Ax')
          then 1
       when (cs.std= 'Bo') 
          then 2
       when (cs.std= 'Ct') 
          then 3
       else null
    end

Thanks in advance

Magickk

Upvotes: 0

Views: 46

Answers (2)

Virgil Ionescu
Virgil Ionescu

Reputation: 337

DISTINCT is slowing down performance on big tables. Instead, a replacement for DISTINCT could be GROUP BY (wich in some scenarios is more rapid)

select * 
from 
(select cs.sch, cs.cls, cs.std, d.date, d.count
from 
(select c.sch, c.cls, s.std
 from 
(select sch, cls  from Data
group by sch, cls) c
cross join
(select std from Data
group by std) s) cs  --every possible combination of school/classes and std
 left outer join 
Data D on D.sch = cs.sch and D.cls = cs.cls and D.std = cs.std  --try and join to the original data
 group by 
     c.sch, c.cls, s.std, d.date, d.count) 
order by 
cs.sch, cs.cls, 
case 
   when (cs.std= 'Ax')
      then 1
   when (cs.std= 'Bo') 
      then 2
   when (cs.std= 'Ct')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

First, the query is generating a lot of rows (presumably) and so it is going to take time.

From what I can tell, the outer aggregation is not necessary. At the very least, you have no aggregation functions which is suspicious.

select c.sch, c.cls, s.std, d.date, d.count
from (Select distinct sch, cls from Data
     ) c cross join    -- list of school/classes
     (select distinct std from Data
     ) s left join     -- list of std
     Data d
     on d.sch = cs.sch and d.cls = cs.cls and d.std = cs.std 
 order by cs.sch, cs.cls, 
          (case cs.std when 'Ax' then 1 when 'Bo' then 2 when 'Ct' else 3 end)

There is nothing you can do about the outer order by. For the select distinct subqueries, you can create indexes on data(sch, cls, std) (the third column is for the join) and data(std).

Upvotes: 2

Related Questions