| Optimization CASE STATEMENT (2 threads merged by bb) [message #394419] |
Thu, 26 March 2009 23:32  |
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: Optimization CASE STATEMENT (2 threads merged by bb) [message #394449 is a reply to message #394419] |
Fri, 27 March 2009 03:22  |
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 |
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 |
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
