Home » RDBMS Server » Performance Tuning » Performance issue in report
Performance issue in report [message #248619] Fri, 29 June 2007 14:52 Go to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
hi expert,

following query retrive about more then 70,000 record but it's too slow.

it's complaince report which show all employees record weither they are active or not.

bold date is input parametere for date.




SELECT
time_empl_id,
sum(time_hours) Actual_Hrs,
time_revm_period revm_date,
--CD
sum(decode(TICH.time_acti_code, 'ACPL', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CLSV', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CSSD', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'COCD', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RSTP', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'REPL', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RCSE', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'ONSC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRCH', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'WHPL', time_hours, 0)) CD_TOTAL,
sum(decode(TICH.time_acti_code, 'ACPL', time_hours, 0)) Account_Planning,
sum(decode(TICH.time_acti_code, 'CLSV', time_hours, 0)) Claims_Services,
sum(decode(TICH.time_acti_code, 'CSSD', time_hours, 0)) Clnt_Strat_Stwrdship_Dialogue,
sum(decode(TICH.time_acti_code, 'COCD', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'RSTP', time_hours, 0)) Renewal_Strategy_Process,
sum(decode(TICH.time_acti_code, 'REPL', time_hours, 0)) Retail_Placement,
sum(decode(TICH.time_acti_code, 'RCSE', time_hours, 0)) Risk_Consulting_Services,
sum(decode(TICH.time_acti_code, 'ONSC', time_hours, 0)) Ongoing_Service_Commitments,
sum(decode(TICH.time_acti_code, 'PEMA', time_hours, 0)) Pema_id,
sum(decode(TICH.time_acti_code, 'TRCH', time_hours, 0)) Travel,
sum(decode(TICH.time_acti_code, 'WHPL', time_hours, 0)) Wholesale_Placement,

--CP
sum(decode(TICH.time_acti_code, 'ENNC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CLTT', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CCSP', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CSSC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'INMR', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'PREX', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RWRK', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRNC', time_hours, 0)) CP_TOTAL,
sum(decode(TICH.time_acti_code, 'ENNC', time_hours, 0)) Entertainment,
sum(decode(TICH.time_acti_code, 'CLTT', time_hours, 0)) Client_Team_Transition,
sum(decode(TICH.time_acti_code, 'CCSP', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'CSSC', time_hours, 0)) Cnfrm_Compensation_wth_Client,
sum(decode(TICH.time_acti_code, 'INMR', time_hours, 0)) Insurer_Market_Relationship,
sum(decode(TICH.time_acti_code, 'PREX', time_hours, 0)) Prospecting_and_Expanded_Srvcs,
sum(decode(TICH.time_acti_code, 'RWRK', time_hours, 0)) Rework,
sum(decode(TICH.time_acti_code, 'TRNC', time_hours, 0)) Travel,

--MT
sum(decode(TICH.time_acti_code, 'ADMN', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CNCS', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'ENMA', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRGV', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRMA', time_hours, 0)) MT_TOTAL,
sum(decode(TICH.time_acti_code, 'ADMN', time_hours, 0)) Administration,
sum(decode(TICH.time_acti_code, 'CNCS', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'ENMA', time_hours, 0)) Entertainment,
sum(decode(TICH.time_acti_code, 'TRGV', time_hours, 0)) Training,
sum(decode(TICH.time_acti_code, 'TRMA', time_hours, 0)) Travel,

--LV
sum(decode(TICH.time_acti_code, 'LVHO', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'FMLA', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'HOLI', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'OTAB', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'SICK', time_hours, 0)) LV_TOTAL,
sum(decode(TICH.time_acti_code, 'LVHO', time_hours, 0)) Company_Holiday,
sum(decode(TICH.time_acti_code, 'FMLA', time_hours, 0)) Family_and_Medical_Leave_Act,
sum(decode(TICH.time_acti_code, 'HOLI', time_hours, 0)) Holiday,
sum(decode(TICH.time_acti_code, 'OTAB', time_hours, 0)) Other_absence,
sum(decode(TICH.time_acti_code, 'SICK', time_hours, 0)) Sickness

FROM CA_TIME_ENTRIES_v TICH
WHERE time_revm_date = to_date('29-apr-2007')
group by time_empl_id,
time_revm_date,
time_revm_period,
time_clie_no

union


SELECT
empl_id,
0,
to_number(to_char(sysdate,'YYYYMM')),
--CD
0,
0 Account_Planning,
0 Claims_Services,
0 Clnt_Strat_Stwrdship_Dialogue,
0 Compliance,
0 Renewal_Strategy_Process,
0 Retail_Placement,
0 Risk_Consulting_Services,
0 Ongoing_Service_Commitments,
0 Pema_id,
0 Travel,
0 Wholesale_Placement,

--CP

0 CP_TOTAL,
0 Entertainment,
0 Client_Team_Transition,
0 Compliance,
0 Cnfrm_Compensation_wth_Client,
0 Insurer_Market_Relationship,
0 Prospecting_and_Expanded_Srvcs,
0 Rework,
0 Travel,

--MT

0 MT_TOTAL,
0 Administration,
0 Compliance,
0 Entertainment,
0 Training,
0 Travel,

--LV

0 LV_TOTAL,
0 Company_Holiday,
0 Family_and_Medical_Leave_Act,
0 Holiday,
0 Other_absence,
0 Sickness
FROM ca_employees
WHERE not exists -- here all employee except those not in bold date
(select 'x'
from te_per_time_entries
where time_revm_date = to_date('29-apr-2007')
and time_empl_number = empl_no);

please advice....
i tried my best but still it's taking about 45 min.....

i run above queries saperatly and it's work fine but when i put union then it become bad...

Thanks in advance.


Re: Performance issue in report [message #248621 is a reply to message #248619] Fri, 29 June 2007 15:03 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
TO_DATE requires a format picture.

In a UNION, the datatypes have to match. Be careful when matching a column that might return a NULL, whereas it is defined as a DATE in the other part of the UNION.
Re: Performance issue in report [message #248623 is a reply to message #248619] Fri, 29 June 2007 15:23 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Thanks for your reply.

All datatype is matched i check already.
also put format mask for date but not good response.

Advice.

Thanks.
Re: Performance issue in report [message #248626 is a reply to message #248619] Fri, 29 June 2007 15:41 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for us that you did not read & FOLLOW the posting guidelines as found in the STICKY posts at the top of this forum.
Please realize that NOBODY can tune SQL by only looking at the code.
If this were possible, a program would tune all SQL automagically.
Since you neglected to post any supporting details, You're On Your Own (YOYO)!
Previous Topic: implications of merging 17 tables into 1...
Next Topic: Avoid Index to be used by Queries
Goto Forum:
  


Current Time: Fri Dec 09 13:54:41 CST 2016

Total time taken to generate the page: 0.10735 seconds