p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle ASP (http://p2p.wrox.com/forumdisplay.php?f=106)
-   -   Increase Performance of main query (http://p2p.wrox.com/showthread.php?t=70457)

vb89 September 8th, 2008 12:26 AM

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



All times are GMT -4. The time now is 10:48 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.