Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vb89
Starting Member
9 Posts |
 Posted - 2008-09-08 : 01:11:51
|
| 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 [/CODE] |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-09-08 : 10:03:43
|
| If you are querying an Oracle database, you should probably ask your questions in an Oracle forum.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
vb89
Starting Member
9 Posts |
Posted - 2008-09-08 : 10:57:08
|
| It should still be the same thing in theory. All i really need to do is adjust the SQL coding that I have above, which should be the same on SQL server |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-09-08 : 11:34:37
|
| indices, indices, indices...on the tables on the key fields that are sued in your where clauses.you also seem to be summarising the same data 3 times? I can't spot a difference anyway?can this be pre-caclulated and saved in a temp table? is it worth sharing/required in other code? |
|
|
|
|
|
|
|
|
Increase performance...