1
\$\begingroup\$

I currently have 6 very similar queries that I'm trying to increase performance on by making them all subqueries in 1 query. I'm not sure what is better performance wise, keeping them 6 separate queries, 1 query with 6 subqueries (as shown below), or some other method that I'm unfamiliar with.

SELECT 
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName) AS total,
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_A BETWEEN 100 AND 199) AS code_a_low_count,
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_A BETWEEN 200 AND 299) AS code_a_high_count,
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_B BETWEEN 100 AND 199) AS code_b_low_count,
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_B BETWEEN 200 AND 299) AS code_b_high_count,
  (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_C BETWEEN 100 AND 199) AS code_c_count
FROM table_one
WHERE ROWNUM=1
\$\endgroup\$
1
\$\begingroup\$

I can suggest you use common table expression (Oracle calls it "Subquery Factoring") at the very least you will be doing the filtering by date and name one time only

WITH filter_by_date_name AS ( 
  SELECT CODE_A, CODE_B, CODE_C FROM table_one WHERE date=myDate AND name=myName
)
SELECT 
  (SELECT COUNT(*) FROM filter_by_date_name) AS total,
  (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_A BETWEEN 100 AND 199) AS code_a_low_count,
  (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_A BETWEEN 200 AND 299) AS code_a_high_count,
  (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_B BETWEEN 100 AND 199) AS code_b_low_count,
  (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_B BETWEEN 200 AND 299) AS code_b_high_count,
  (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_C BETWEEN 100 AND 199) AS code_c_count
FROM filter_by_date_name
WHERE ROWNUM=1
\$\endgroup\$
1
\$\begingroup\$

This is a simple task for conditional aggregation using CASEs:

SELECT 
  COUNT(*) AS total,
  COUNT(CASE WHEN CODE_A BETWEEN 100 AND 199 THEN 1 END) AS code_a_low_count,
  COUNT(CASE WHEN CODE_A BETWEEN 200 AND 299 THEN 1 END) AS code_a_high_count,
  COUNT(CASE WHEN CODE_B BETWEEN 100 AND 199 THEN 1 END) AS code_b_low_count,
  COUNT(CASE WHEN CODE_B BETWEEN 200 AND 299 THEN 1 END) AS code_b_high_count,
  COUNT(CASE WHEN CODE_C BETWEEN 100 AND 199 THEN 1 END) AS code_c_count
FROM table_one
WHERE date=myDate AND name=myName

As you're COUNTing you can return anything (besides NULL) in THEN.

This can also be written as a SUM over 1/0:

SUM(CASE WHEN CODE_A BETWEEN 100 AND 199 THEN 1 ELSE 0 END)
\$\endgroup\$
1
\$\begingroup\$

Yes, this is a late answer; nonetheless, here you go, a different approach: instead of querying the same table "n" times, do it once. Instead of count, use sum with decode.

select 
  count(*) total,
  sum(case when code_a between 100 and 199 then 1 else 0 end) code_a_low_count,
  sum(case when code_a between 200 and 299 then 1 else 0 end) code_a_high_count,
  sum(case when code_b between 100 and 199 then 1 else 0 end) code_b_low_count,
  sum(case when code_b between 200 and 299 then 1 else 0 end) code_b_high_count,
  sum(case when code_c between 100 and 199 then 1 else 0 end) code_c_count
from table_one
where date = mydate
and name = myname;
\$\endgroup\$
0
\$\begingroup\$

The real Jedi Knight way here is to use the force of PIVOT/UNPIVOT feature.

with t as (
    select 'myname' myname, sysdate mydate, 100 CODE_A, 199 CODE_B, 222 CODE_C from dual union all
    select 'myname' myname, sysdate mydate, 234 CODE_A, 200 CODE_B, 135 CODE_C from dual union all
    select 'myname' myname, sysdate mydate, 155 CODE_A, 124 CODE_B, 299 CODE_C from dual
    ),
  precalc as (
    select myname, mydate, floor(CODE_A/100) CODE_A, floor(CODE_B/100) CODE_B, floor(CODE_C/100) CODE_C
      from t),
  unpiv as (
    select myname, mydate, col_name || col_val name_and_val
      from precalc
   unpivot (col_val for col_name in ("CODE_A","CODE_B","CODE_C")))
select *
  from unpiv
 pivot (count(*) for name_and_val in (
   'CODE_A1' code_a_low_count, 'CODE_A2' code_a_high_count, 
   'CODE_B1' code_b_low_count, 'CODE_B2' code_b_high_count, 
   'CODE_C1' code_c_low_count, 'CODE_C2' code_c_high_count))

How does it work:

  • t subquery contain sample data;
  • precalc replaces different numeric values with 1 and 2 for *_low_count and *_high_count;
  • unpiv is result of unpivoting (COL_NAME and COL_VAL are added below to demonstrate the idea, they not present in the query):

    MYNAME MYDATE   COL_NAME    COL_VAL NAME_AND_VAL 
    ------ -------- -------- ---------- -------------
    myname 13/09/18 CODE_A            1 CODE_A1      
    myname 13/09/18 CODE_B            1 CODE_B1      
    myname 13/09/18 CODE_C            2 CODE_C2      
    myname 13/09/18 CODE_A            2 CODE_A2      
    myname 13/09/18 CODE_B            2 CODE_B2      
    myname 13/09/18 CODE_C            1 CODE_C1      
    myname 13/09/18 CODE_A            1 CODE_A1      
    myname 13/09/18 CODE_B            1 CODE_B1      
    myname 13/09/18 CODE_C            2 CODE_C2 
    
  • Final query is a pivot query calculating counts of occurancies of values CODE_A1, CODE_B1, etc.:

    MYNAME MYDATE   CODE_A_LOW_COUNT CODE_A_HIGH_COUNT CODE_B_LOW_COUNT CODE_B_HIGH_COUNT CODE_C_LOW_COUNT CODE_C_HIGH_COUNT
    ------ -------- ---------------- ----------------- ---------------- ----------------- ---------------- -----------------
    myname 13/09/18                2                 1                2                 1                1                 2
    
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.