View Single Post
  #1 (permalink)  
Old September 8th, 2008, 12:26 AM
vb89 vb89 is offline
Authorized User
Points: 153, Level: 3
Points: 153, Level: 3 Points: 153, Level: 3 Points: 153, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , .
Posts: 37
Thanks: 5
Thanked 0 Times in 0 Posts
Default Increase Performance of main query

I am currently running an application which run dynamically. The current problem that i have is that when i run my app. it times out because the main query takes too long to run. If anyone could suggest anyway anyway that i could increase the performance i would appreciate it.
**The app is written in asp.net and im using oracle database, and unfortunatly i can't make this a stored procedure, so any suggestions on modifying what i currently have would be great**

Code:

Code:
FROM TABLE 
    tDataSQL = tDataSQL &" FROM customer_data.cd_bk_player_ytd_stats main," &_ 
    "(SELECT team_id, game_type_id, league_id, team_id_1032, season_id, SUM(plus_minus) AS plus_minus " &_ 
    " FROM customer_data.cd_bk_player_game_stats " &_ 
    " WHERE season >= " & iSeasonMinPlusMinus &_ 
    " AND split_number = 0" &_ 
    " AND game_type_id =" & iGameType &_ 
    " GROUP BY player_id, team_id, game_type_id, league_id, team_id_1032, season_id) gs" 

    If iRosterType = ACTIVEPLAYERS Then 
    tDataSQL = tDataSQL &" customer_data.cd_bk_roster ros," &_ 
    "(SELECT player_id, player_id_1032, league_id, team_id," &_ 
    " team_id_1032, conf_id, position_id, season_id, SUM(pus_minus) AS plus_minus " &_ 
    " FROM customer_data.cd_bk_player_game_stats " &_ 
    " WHERE season >= " & iSeasonMinPlusMinus &_ 
    " AND split_number = 0" &_ 
    " AND game_type_id =" & iGameType &_ 
    " GROUP BY player_id, player_id_1032, league_id, team_id, team_id_1032, conf_id, position_id, season_id) gs" 

    End If 

    tOppSQL = Replace(tTotalSQL,"main.","main.opp_") &" FROM customer_data.cd_bk_team_ytd_stats main," &_ 
    "(SELECT team_id, game_type_id, league_id, team_id_1032, season_id, SUM(plus_minus) AS plus_minus " &_ 
    " FROM customer_data.cd_bk_player_game_stats " &_ 
    " WHERE season >= " & iSeasonMinPlusMinus &_ 
    " AND split_number = 0" &_ 
    " AND game_type_id =" & iGameType &_ 
    " GROUP BY team_id, game_type_id, league_id, team_id_1032, season_id) gs" 


    tTotalSQL = tTotalSQL &" FROM customer_data.cd_bk_team_ytd_stats main," &_ 
    "(SELECT team_id, game_type_id, league_id, team_id_1032, season_id, SUM(plus_minus) AS plus_minus " &_ 
    " FROM customer_data.cd_bk_player_game_stats " &_ 
    " WHERE season >= " & iSeasonMinPlusMinus &_ 
    " AND split_number = 0" &_ 
    " AND game_type_id =" & iGameType &_ 
    " GROUP BY team_id, game_type_id, league_id, team_id_1032, season_id) gs" 


 'WHERE CLAUS (FILTERS) 

  tWhereSQL = " WHERE main.active_record <> 'R' "&_ 
                  " AND main.game_type_id ="& iGameType &_ 
                  " AND main.split_number = -1" &_ 
                " AND main.league_id IN (1,6)" &_ 
                " AND main.season = "& iSeason &_ 
                  " AND main.team_id_1032 = " & iID &_ 
                " AND gs.team_id = main.team_id" &_ 
                " AND gs.game_type_id = main.game_type_id" &_ 
                " AND gs.league_id = main.league_id" &_ 
                " AND gs.team_id_1032 = main.team_id_1032" &_ 
                " AND gs.season_id = main.season_id"     

  tDataSQL = tDataSQL & tWhereSQL 

  If iRosterType = ACTIVEPLAYERS Then 
    tDataSQL = tDataSQL &" AND main.player_id = ros.player_id" &_ 
                  " AND main.league_id = ros.league_id" &_ 
                 " AND ros.team_id = main.team_id" &_ 
                  " AND ros.status = 'Y'" &_ 
                " AND gs.player_id = ros.player_id" &_ 
                " AND gs.player_id_1032 = ros.player_id_1032" &_ 
                " AND gs.player_id = ros.player_id" &_ 
                " AND gs.leauge_id = ros.league_id" &_ 
                " AND gs.team_id = ros.team_id" &_ 
                " AND gs.team_id_1032 = ros.team_id_1032" &_ 
                " AND gs.conf_id = ros.conf_id" &_ 
                " AND gs.season_id = ros.season_id" &_ 
                " AND gs.player_id = main.player_id" &_ 
                " AND gs.player_id_1032 = main.player_id_1032" &_ 
                " AND gs.player_id = main.player_id" &_ 
                " AND gs.leauge_id = main.league_id" &_ 
                " AND gs.team_id = main.team_id" &_ 
                " AND gs.team_id_1032 = main.team_id_1032" &_ 
                " AND gs.conf_id = main.conf_id" &_ 
                " AND gs.season_id = main.season_id" 
  End If 
  tTotalSQL = tTotalSQL & tWhereSQL 
  tOppSQL = tOppSQL & tWhereSQL