Home » SQL & PL/SQL » SQL & PL/SQL » Optimization CASE STATEMENT (2 threads merged by bb)
Optimization CASE STATEMENT (2 threads merged by bb) [message #394419] Thu, 26 March 2009 23:32 Go to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
My report become very slow when i use this code in oracle report ,

Please how can we optimize this





select case
when (select count(payscale_id_pk) from assignments where empid = e.empid
and dept_sec_id = ds.dept_sec_id
and dateto is null ) > 1
AND (select count(distinct payscale_id_pk) from assignments
where empid = e.empid
and dept_sec_id = ds.dept_sec_id
and dateto is null) = 1
then (select distinct payscale_id_pk from assignments
where empid = e.empid
and dept_sec_id = ds.dept_sec_id
and dateto is null)
when (select count(payscale_id_pk) from assignments
where empid = e.empid
and dept_sec_id = ds.dept_sec_id and dateto is
null ) > 1
AND (select count(distinct payscale_id_pk) from assignments where empid = e.empid and dept_sec_id = ds.dept_sec_id and
dateto is null) > 1
then (select to_char(max(to_number(payscale_id_pk))) from assignments where empid = e.empid and dept_sec_id = ds.dept_sec_id and
to_number(payscale_id_pk) <> 23 and dateto is null)
when (select count(payscale_id_pk) from assignments where empid = e.empid and dept_sec_id = ds.dept_sec_id and dateto is
null) = 1
then (select payscale_id_pk from assignments where empid = e.empid and dept_sec_id = ds.dept_sec_id and dateto is
null)
end as payscale_id_pk
from dual

Re: Optimizationn CASE STATEMENT [message #394422 is a reply to message #394419] Fri, 27 March 2009 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/
Re: Optimizationn CASE STATEMENT [message #394423 is a reply to message #394419] Fri, 27 March 2009 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: Optimizationn CASE STATEMENT [message #394443 is a reply to message #394423] Fri, 27 March 2009 02:50 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
[sorry: Post Deleted] Sad
Thanks
Trivendra

[Updated on: Fri, 27 March 2009 02:53]

Report message to a moderator

Re: Optimization CASE STATEMENT (2 threads merged by bb) [message #394449 is a reply to message #394419] Fri, 27 March 2009 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By throwing it away and starting again.

.You can write your query symbolicaly like this:
select case 
 when (:1 ) > 1 
 AND ( :2) = 1 
 then ( :3 )
 when ( :1 ) > 1 
 AND ( :2) > 1 
 then ( :4 )
 when ( :1 ) = 1 
 then ( :5 )
 end as payscale_id_pk
 from dual 

 1 select count(payscale_id_pk) 
   from assignments 
   where empid = e.empid 
   and dept_sec_id = ds.dept_sec_id 
   and dateto is null

 2 select count(distinct payscale_id_pk) 
   from assignments 
   where empid = e.empid 
   and dept_sec_id = ds.dept_sec_id 
   and dateto is null

 3 select distinct payscale_id_pk 
   from assignments 
   where empid = e.empid 
   and dept_sec_id = ds.dept_sec_id 
   and dateto is null

 4 select to_char(max(to_number(payscale_id_pk))) 
   from assignments 
   where empid = e.empid 
   and dept_sec_id = ds.dept_sec_id 
   and to_number(payscale_id_pk) <> 23 
   and dateto is null

 5 select payscale_id_pk 
   from assignments 
   where empid = e.empid 
   and dept_sec_id = ds.dept_sec_id 
   and dateto is  null


Now, there's a lot of duplication in these tests.
The results returned by :3 and :5 will always be the same, so the first and last terms in the CASE can be combined.
If :2 >1 then it follows that :1 must be > 1 as well, so the first part of the second clause can be dropped.

Now as to the structure of performing multiple queries against your table - it's really poor. I'll need to do some tests to see whether it actually evaluates all the test queries, of if it just steps through the list until it gets to a test that passes, but either way in the worst case you'll be hitting the same table 6 times to get an answer.

I think this can be rewritten as
select case when cnt_dist_pk > 1 then payscale_id_pk_not_23
             else payscale_id_pk
             end
 from (select count(distinct payscale_id_pk)   cnt_dist_pk
             ,max(to_number(payscale_id_pk)    payscale_id_pk
             ,max(case when to_number(payscale_id_pk) =23 then 0 
                       else to_number(payscale_id_pk) end) payscale_id_pk_not_23
       from assignments 
       where empid     = e.empid 
       and dept_sec_id = ds.dept_sec_id 
       and dateto is null)
Re: Optimization CASE STATEMENT (2 threads merged by bb) [message #394585 is a reply to message #394449] Fri, 27 March 2009 23:55 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
when i run the query given below
it give only 3 employees


select distinct(e.empid), js.empjobstatus_value,intype.indtype, intype.induction_id_pk, e.map_id,e.chargender, e.dateofbirth, e.nicno, ' '||E.TITLE||' '||
initcap(e. empname) AS EMPNAME, e.joiningdate , e.pfnumber , a.assignment_id_pk,
--, a.datefrom ,a.desig_id, a.assignment_id_pk, des.vchardesigname
d.VCHARDEPT_NAME, d.department_id_pk, to_number(p.payscale_id_pk), p.vcharpayscale payscale , ds.dept_sec_id
from employee e,assignments a, designation des, department d ,department_section ds, payscale p , induction_type intype ,emp_jobstatus js
where p.payscale_id_pk = a.payscale_id_pk
and a.dateto is null
and a.induction_id = intype.induction_id_pk
and d.department_id_pk = ds.department_id_pk
and e.empjobstatus_id=js.empjobstatus_id
and e.empid=a.empid
and ds.dept_sec_id = a.dept_sec_id
and des.desig_id=a.desig_id
and a.operation_id not in (2,3)
and a.assignment_id_pk in
(select distinct a.assignment_id_pk
from employee e,assignments a, designation des, department d , department_section ds, payscale p ,emp_jobstatus js
where p.payscale_id_pk=a.payscale_id_pk
and a.dateto is null
and e.empjobstatus_id=js.empjobstatus_id
and d.department_id_pk = ds.department_id_pk
and e.empid = a.empid
and ds.dept_sec_id = a.dept_sec_id
and des.desig_id = a.desig_id
and a.operation_id not in (2,3)
and js.empjobstatus not like ('FS%')
and e.empjobstatus_id=js.empjobstatus_id
and a.payscale_id_pk IN
(
select case when cnt_dist_pk > 1 then payscale_id_pk_not_23
else payscale_id_pk
end
from
(
select count(distinct payscale_id_pk) cnt_dist_pk
,max(to_number(payscale_id_pk) ) payscale_id_pk
,max
(
case when to_number(payscale_id_pk) =23 then 0
else
to_number(payscale_id_pk)
end
)
payscale_id_pk_not_23
from
assignments a ,employee e ,department_section ds
where a.empid = e.empid
and a.dept_sec_id = ds.dept_sec_id
and a.dateto is null
)
)
and d.department_id_pk = :department_id_pk1

)


Re: Optimization CASE STATEMENT (2 threads merged by bb) [message #394826 is a reply to message #394585] Mon, 30 March 2009 07:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How is this in any way connected to your previous post?

Is 3 the number of rows you were expecting?
Given that Oracle will be returning the correct number of rows, why do you feel that the number returned should be different?

Given that we have none of
-DDL for your tables
-DML for your data
-Values for the bind variables that you used
-An explanation of what you are trying to achieve

What help are you expecting?
Previous Topic: where the Select statement output will be stored
Next Topic: How to insert dynamically through a variable in PL/SQL??
Goto Forum:
  


Current Time: Sat Dec 10 20:18:44 CST 2016

Total time taken to generate the page: 0.13331 seconds