Thread: slow count(CASE) query
<span class="Apple-style-span" style="font-family: verdana, geneva, helvetica, arial, sans-serif; font-size: 13px; border-collapse:collapse; ">Hi all, <br /><br />I have this kind of query that I need to do, yes my query is giving rightanswers now but it is long and slow. I am now asking you that if <br /> you have another solution for my query to makethat more smarter ! Hope you can help me with this ! <br /><br /><br />select '000100' as length, sum(ship1) as ship1,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM <br /> (select <br />count(CASEWHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br />count(CASE WHEN (type between 60 and 69)THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br />count(CASE WHEN (type >90) THEN 1 ELSENULL END) as ship5 <br />FROM school_proj_boat where length <100 <br />GROUP BY type <br />ORDER BY type) as koo <br/><br />UNION ALL <br /><br />select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4)as ship4,sum(ship5) as ship5 FROM <br />(select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSENULL END) as ship1, <br /> count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASEWHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br /> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boatwhere length between 100 and 200 <br />GROUP BY type <br />ORDER BY type) as koo <br /><br />UNION ALL <br/><br />select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5)as ship5 FROM <br /> (select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br/>count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between70 and 79) THEN 1 ELSE NULL END) as ship3, <br /> count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END)as ship4, <br />count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boat where lengthbetween 200 and 300 <br />GROUP BY type <br /> ORDER BY type) as koo <br /><br />UNION ALL <br /><br />select '300999'as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM <br/>(select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br /> count(CASE WHEN(type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between 70 and 79) THEN 1 ELSENULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br /> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boat where length >300 <br />GROUPBY type <br />ORDER BY type) as koo</span>
Grant Masan wrote: > Hi all, > > I have this kind of query that I need to do, yes my query is giving right > answers now but it is long and slow. I am now asking you that if > you have another solution for my query to make that more smarter ! Hope you > can help me with this ! > > > select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length <100 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 100 and 200 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 200 and 300 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length >300 > GROUP BY type > ORDER BY type) as koo > Well, it looks like this will read school_proj_boat 4 times. What about 1) A plsql function that iterates *one time* on school_proj_boat, with anested CASE, or a par of IF's 2) Could be a good place for using window functions http://www.postgresql.org/docs/current/static/tutorial-window.html HTH Gerardo
Grant Masan wrote: > Hi all, > > I have this kind of query that I need to do, yes my query is giving > right answers now but it is long and slow. I am now asking you that if > you have another solution for my query to make that more smarter ! Hope > you can help me with this ! > > > select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length <100 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 100 and 200 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 200 and 300 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length >300 > GROUP BY type > ORDER BY type) as koo Not thrilled with the "coded" length in the returned record but you could CASE that too and then you would only read the data once. If you need the explicit length range in the final result you could do select mod(length,100) * 100 as low_length, (mod(length,100) + 1) * 100 as high_length.... group by low_length, high_length, type You would of course get more type of records 400-500, 500-600 etc but again it all happens in a single read. And temp tables can be your friend too!
Grant Masan wrote:
>
> select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
> (select
> count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1,
> count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
> count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4,
> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
> FROM school_proj_boat where length <100
> GROUP BY type
> ORDER BY type) as koo
>
> UNION ALL
>
> select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as
...
> FROM school_proj_boat where length between 100 and 200
> GROUP BY type
> ORDER BY type) as koo
>
> UNION ALL
...
First thing is to ditch the UNION ALLs. You're basically repeating the
same query.
Create a lookup table: length_codes (code, min_length, max_length)
Data: ('100100', 0, 99), ('100200', 100, 199), ...
SELECT length_code AS length, sum...
FROM ( SELECT lc.code AS length_code, count(case)... FROM school_proj_boat spb, length_codes lc WHERE
spb.lengthBETWEEN lc.min_length AND lc.max_length ) AS koo
;
It's easy to forget that you can join against a table using any
condition, it doesn't have to be equality. Here we use BETWEEN to
replace our UNIONs.
You'll want a unique constraint on length_codes.code and you should
really write a custom trigger to make sure none of the
min_length..max_length ranges overlap. In practice, you're probably only
setting this table up once so might not bother.
-- Richard Huxton Archonet Ltd