Home » SQL & PL/SQL » SQL & PL/SQL » tuning a sql
tuning a sql [message #320205] Wed, 14 May 2008 05:29 Go to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hello,all
I have a sql like this

               select
                  player_name
                 , division_id
                 , division_name
                 , player_jersey
                 , team_id
                 , team_name
                 , count(distinct game_goal_game, game_goal_team, game_goal_game_team_seq) as G
                 , count(distinct game_assist_game, game_assist_team, game_assist_game_team_seq) as A
                 , count(distinct game_goal_game, game_goal_team, game_goal_game_team_seq)+ count(distinct game_assist_game, game_assist_team, game_assist_game_team_seq) as Pts
                 , coalesce(round(sum(game_penalty_minutes) / count(distinct game_goal_game, game_goal_team, game_goal_game_team_seq) / count(distinct game_assist_game, game_assist_team, game_assist_game_team_seq)),0) as PIM
                from player
                  inner join team on player_team = team_id
                  inner join division on team_division = division_id
                     inner join league on division_league = league_id

                  left outer join game_goal on game_goal_player = player_id
                  left outer join game_assist on game_assist_player = player_id
                  left outer join game_penalty on game_penalty_player = player_id
                where league_season = 200709
                     and league_stats = 2
                  and player_jersey <> 'EMPTY'
                  and player_jersey <> 'RENT'
                 group by player_name, player_jersey, team_id, team_name, division_id, division_name
                 order by Pts desc, G desc, A desc, PIM, player_name
                 limit 20


I am sorry because it was a mysql sql statement,could someone rewrite it with standard sql to get a better performence.
I think the distinct part is bad.

Regards
Alan
Re: tuning a sql [message #320214 is a reply to message #320205] Wed, 14 May 2008 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read How to Identify Performance Problem and Bottleneck and OraFAQ Forum Guide, Performance Tuning section, and provide the requested information.

Regards
Michel
Re: tuning a sql [message #320257 is a reply to message #320205] Wed, 14 May 2008 07:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
alantany wrote on Wed, 14 May 2008 12:29
,could someone rewrite it with standard sql to get a better performence.



How much do you offer?
You have to be kidding, or you phrased your question ("could someone help me? I am rewriting this and I got as far as <insert your code here>, but <insert your error here>")
Re: tuning a sql [message #320263 is a reply to message #320205] Wed, 14 May 2008 08:03 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> limit 20
Why are you posting MYSQL query to this Oracle forum?
Previous Topic: Materialized views - create and refresh times
Next Topic: Drop partitioned table
Goto Forum:
  


Current Time: Wed Dec 07 13:00:30 CST 2016

Total time taken to generate the page: 0.06722 seconds