esteban rincon esteban rincon -3 years ago 72
SQL Question

Solving query performance issues

I've been having trouble knowing/understanding how to optimize the following query, I'm pretty new to performance hints so maybe someone could give a fiew pointers:

btw, I need to keep all info from left table (event_user) thus the left join but the cost is just outrageous! So when I call this query in

SQL-Dev
it just stays there 'loading' and takes ages and i don't know how to aproach this.

Query



SELECT event_user.*,
dw_attendee.*
FROM event_user
LEFT JOIN dw_attendee ON event_user.event_user_id = dw_attendee.event_user_id
AND event_user.event_id = dw_attendee.event_id
AND dw_attendee.session_id = 1
AND event_user.event_id = :eventid;


Plan



-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 130M| 44G| | 2754K (2)| 10:42:44 | | |
|* 1 | HASH JOIN RIGHT OUTER| | 130M| 44G| 4513M| 2754K (2)| 10:42:44 | | |
|* 2 | TABLE ACCESS FULL | DW_ATTENDEE | 57M| 3861M| | 106K (8)| 00:24:45 | | |
| 3 | PARTITION RANGE ALL | | 130M| 35G| | 831K (5)| 03:14:08 | 1 | 6044 |
| 4 | TABLE ACCESS FULL | EVENT_USER | 130M| 35G| | 831K (5)| 03:14:08 | 1 | 6044 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EVENT_USER"."EVENT_ID"="DW_ATTENDEE"."EVENT_ID"(+) AND
"EVENT_USER"."EVENT_USER_ID"="DW_ATTENDEE"."EVENT_USER_ID"(+) AND "EVENT_USER"."EVENT_ID"=CASE WHEN
("DW_ATTENDEE"."EVENT_USER_ID"(+) IS NOT NULL) THEN 2002317 ELSE 2002317 END )
2 - filter("DW_ATTENDEE"."SESSION_ID"(+)=1)


My initial thought was to perform a query like the following, since each query performed separately is extreemely fast (cost 0)

with reg as (select * from event_user where event_id = :eventid),
atd as (select * from dw_attendee where event_id = :eventid)
select distinct reg.*, atd.*
from reg left join atd on reg.event_id = atd.event_id;


The above query is great in terms of cost (172) but my resultset from this query is too extensive, it is literaly repeating each row by 20.

My left table query:

select * from event_user where event_id = 2002317;


returns 30 rows, the
with reg as...
query returns 600! Each row*20, any pointers ? thank you

Answer Source

in your query, the fact that you put the event_user.event_id = :eventid in the join condition, it means that you are not filtering event_user table. Just put it in the where and it will be faster. Otherwitse, you bring back the whole event_user table but try to join only for one particular event_id.

SELECT event_user.*,
       dw_attendee.*
FROM event_user
LEFT JOIN dw_attendee ON event_user.event_user_id = dw_attendee.event_user_id
                      AND event_user.event_id = dw_attendee.event_id
                      AND dw_attendee.session_id = 1
where event_user.event_id = :eventid;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download