| Help in tuning query [message #344858] |
Mon, 01 September 2008 07:55  |
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
|
Junior Member |
|
|
SELECT ah.root_agent_id,
SUM (CASE WHEN ld_status_id = 1 THEN 1 ELSE 0 END) stat_new,
SUM (CASE WHEN ld_status_id = 2 THEN 1 ELSE 0 END) stat_open,
SUM (CASE WHEN ld_status_id = 3 THEN 1 ELSE 0 END) stat_fftd,
SUM (CASE WHEN ld_status_id = 4 THEN 1 ELSE 0 END) stat_pres,
SUM (CASE WHEN ld_status_id = 5 THEN 1 ELSE 0 END) stat_posi,
SUM (CASE WHEN ld_status_id = 6 THEN 1 ELSE 0 END) stat_hibe,
SUM (CASE WHEN ld_status_id = 7 THEN 1 ELSE 0 END) stat_nega, SUM (CASE WHEN ld_status_id = 8 THEN 1 ELSE 0 END)stat_esc_new,
SUM (CASE WHEN ld_status_id = 9 THEN 1 ELSE 0 END) stat_esc_open,
SUM (CASE WHEN ld_status_id = 10 THEN 1 ELSE 0 END) stat_esc_fftd,
SUM (CASE WHEN ld_status_id = 11 THEN 1 ELSE 0 END) stat_esc_pres,
SUM (CASE WHEN ld_status_id = 12 THEN 1 ELSE 0 END) stat_ffd,
SUM (CASE WHEN ld_status_id = 13 THEN 1 ELSE 0 END) stat_esc_ffd
FROM (SELECT fn_get_nth_agent_id(hierarchy_path,'.',9) AS root_agent_id,a.channel, a.agent_id
FROM ams_agent_hierarchy a, ams_agent_master b
WHERE a.agent_id = b.agent_id
and a.hierarchy_level >= 9
and b.active = 'A') ah
LEFT OUTER JOIN
ams_lts_lead_details pl
ON ah.agent_id = pl.ld_allocate_to_id
GROUP BY ah.root_agent_id
Explain plan
SELECT STATEMENT CHOOSECost: 1 K Bytes: 5 K Cardinality: 48
6 HASH GROUP BY Cost: 1 K Bytes: 5 K Cardinality: 48
5 HASH JOIN OUTER Cost: 1 K Bytes: 14 M Cardinality: 146 K
3 HASH JOIN Cost: 1 K Bytes: 564 K Cardinality: 6 K
1 TABLE ACCESS FULL TABLE AMS.AMS_AGENT_HIERARCHY Cost: 807 Bytes: 494 K Cardinality: 6 K
2 INDEX FAST FULL SCAN INDEX AMS.IDX_AGENTMASTER_DESIGNATION Cost: 361 Bytes: 2 M Cardinality: 227 K
4 INDEX FAST FULL SCAN INDEX AMS.IDX_LEAD_DTL_ALLOC_TO Cost: 123 Bytes: 2 M Cardinality: 145 K
[Updated on: Mon, 01 September 2008 07:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Help in tuning query [message #344873 is a reply to message #344858] |
Mon, 01 September 2008 08:31  |
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
|
Member |
|
|
Also review the index on the table ams_agent_hierarchy.
Check if there is scope of adding an index on a.hierarchy_level
so that your search might opt for a index range scan instead of a full table scan.
Regards,
Dwarak.K
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help in tuning query [message #348382 is a reply to message #344858] |
Tue, 16 September 2008 10:27 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
please listen to rleishman, he seems to be one of the smartest members there.
If he sed to rewrite query without UDF, so do it.
Because using event simple UDF in select it slow down it minimum 10 times.
you can use option PREDICATED in UDF (if UDF is predicated for true) so then performance loss can be minimum.
other thing, please post server details, tables stats, and time of execution.
|
|
|
|
|
|
| Re: Help in tuning query [message #348520 is a reply to message #348467] |
Wed, 17 September 2008 01:57 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Hi Trupti111,
When you say the function is taking 12 sec to display output
do you mean that the select statement, with the function, now
takes 12 secs - what does this represent/how was it isolated?
I'd shoot for divide-and-conquer here- how does the SELECT within the ah dataset execute alone without the outer join?
I'd like to see the output (and alias for each field); my instinct says to
(1) test the subselect for dataset AH alone, with the function
(2) test the subselect for AH with the outer join without
the function
Just making sure you've truly benchmarked the function's performance separate from AH & The outer join, and separate from the SUM/Group by of the top select.
Does that function just parse out a piece of a string with a position relative to a delimiter? Im not sold on it being the culprit without knowing how its performance was isolated/tested.
How long does this take vs the whole select:
SELECT fn_get_nth_agent_id(hierarchy_path,'.',9) AS root_agent_id,a.channel, a.agent_id
FROM ams_agent_hierarchy a, ams_agent_master b
WHERE a.agent_id = b.agent_id
and a.hierarchy_level >= 9
and b.active = 'A')
I'm wondering about DECODING the id_status_id for your stat fields and summing those results; i'd need to see the output
and know the origin of the non-aliased fields in the query.
Best Regards
Harry
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
