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