CODEWITHSUNDEEP
sqloracle-database
earachefl
earachefl

Reputation: 1890

How could this query be improved?

The following query works, but is not particularly fast:

select distinct controlid, (
                select max(Orderno) From my_table
                Where ControlID = #param1#
                    and a = 3 and b = 13 and c = 0 and d = 0
            ) colA, (
                Select max(Orderno) From my_table
                Where ControlID = #param1#
                    and a = 2
            ) colB, (
                Select max(Orderno) From my_table
                Where ControlID = #param1# 
                    and a = 1 and b = 14 and e = 1
            ) colC, (
                Select max(Orderno) From my_table
                Where ControlID = #param1# 
                    and a = 3 and b = 13 and e = 1 and c = 0 and d = 0
            ) colD, (
                Select a From my_table
                Where ControlID = #param1#
                    and Orderno = #param2#  
            ) colE
            from my_table
            where controlid = #param1#

The table being queried has over 300K rows, and the count of rows for a particular controlid (before the distinct clause) varies from 1 to 61. The profiler says the response time is 234/42 msecs. There are no indexes on the table.

Obviously, the lack of indexes is a problem, but beyond that, can anyone recommend a more efficient way of writing this query?

Upvotes: 1

Views: 71

Answers (2)

alzaimar
alzaimar

Reputation: 4622

use an aggregate instead of subqueries:

select distinct controlid, 
  max (case when a=3 and b=13 and c=0 and d= 0 then OrderNo end) as colA,
  max (case when a=2                           then OrderNo end) as colB,
  max (case when a=1 and b=14 and e=1          then OrderNo end) as colC,
  max (case when a=3 and b=13 and e=1 and c=0 and d=0 then OrderNo end) as colD,
  max (case when OrderNo=#param2# then a end) as colE
from my_table
where controlid = #param1#
group by controlid

I don't know which RDBMS you use, so the case when construct might have to be modified to your local dialect. This one should be valid for MSSQL

UPDATE: My glasses don't work properly and so I did not notice the ORACLE tag. Embarassing...

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231871

You should be able to avoid hitting the table multiple times and do a single table scan

SELECT controlID,
       MAX( CASE WHEN a = 3
                  AND b = 13
                  AND c = 0 
                  AND d = 0
                 THEN orderNo
                 ELSE NULL
              END) colA,
       MAX( CASE WHEN a = 2
                 THEN orderNo
                 ELSE NULL
              END) colB,
       MAX( CASE WHEN a = 1
                  AND b = 14
                  AND e = 1
                 THEN orderNo
                 ELSE NULL
              END) colC,
       MAX( CASE WHEN a = 3
                  AND b = 13
                  AND e = 1
                  AND c = 0
                  AND d = 0
                 THEN orderNo
                 ELSE NULL
              END) colD,
       MAX( CASE WHEN orderNo = #param2#
                 THEN a
                 ELSE NULL
              END) colE
  FROM my_table
 WHERE controlId = #param1#
 GROUP BY controlID

Of course, it may well end up being more efficient to have appropriate indexes on the table and do multiple index scans. Without knowing your data, it's hard to know.

Upvotes: 3

Related Questions