Help with query...

[jbachris]

...I just redesigned my database thanks to a tip from one poster on here how to set it up. I have three tables

TABLE ncaa_gm
gameID
game_date
game_fixed

TABLE ncaa_gm_info
infoID
gameID (fk)
teamID (fk)
team_score

TABLE ncaa_tm
teamID
team_name
team_rating
team_ww

I need it to run through each game and calculate the game rating for the team.

If you win the rating is:

$weighted_win = $team_ww + $opp_rating + 1;

If you lose the rating is:

$weighted_win = $team_ww + $opp_rating - 1;

I have tried to run this as my main query:

$weighted_rs = $conn->Execute ( "SELECT ncaa_gm.gameID, ncaa_gm.game_date, game_fixed, infoID, ncaa_gm_info.gameID, ncaa_gm_info.teamID, ncaa_gm_info.team_score FROM ncaa_gm_info, ncaa_gm WHERE ncaa_gm_info.gameID = ncaa_gm.gameID AND game_fixed = 1 ORDER BY infoID ASC" ) or die ( $conn->ErrorMsg() );

Then I have two queries in the loop that get the teams info from that game and then the opponents info in a different query.

This just takes for ever and for some reason isn't getting the right results.

I was just interested in other's thoughts on the best way to setup a query (or queries) to accomplish this...

[johanafm]

Does changing the stuff from "FROM" up to "WHERE" to:
FROM ncaa_gm_info INNER JOIN ncaa_gm ON ncaa_gm.gameID = ncaa_gm.gameID

do the trick?

[jbachris]

Nope.

I have over 11,500 games (thus over 23,000 game_info entries) in the database. All games from 2004, 2005, 2006, and soon 2007. So an average of around 3,800 per year.

After 10 minutes of running the script it times out and stops.

The ratings are ran through an iteration until they normalize.

I think my issue may be that every time through the iteration the script goes through all the games again, finding which ones are = year and where game_fixed = 1.

So the script goes through all games and calculates the rating for each team. Then it checks the convergence to see if the difference in the new rating and previous rating is < 0.00005. If it is greater than that then it runs through every game again and calculates the rating again, then checks convergence, etc, etc.

When I run it with a much smaller sample of games it runs just fine, but when I get up to the 3,800 that I need for college football it takes too long and times out.

I just need to fix my query, I think, and better optimize it to increase speed. I had another structure of the database I used before that included both teamID's and team scores in the games TABLE, but I was told that was a bad database structure. The ratings worked with that, but since I change the structure it takes too long.

Is it bad structure to run a loop within a loop? I could run an initial query to get all the teams from their TABLE, then within that loop run a query that gets all the game info from games_info TABLE where loop1.teamID = loop2.teamID and calculate the ratings that way.

[Piranha]

Since the query doesn't change anything in the database I assume that you have some PHP code to set the rating. To be able to help you I think we have to see that code as well.

[jbachris]

Here's the code. I like the new DB structure 🙂

$weighted_rs = $conn->Execute ( "SELECT ncaa_gm.gameID, ncaa_gm.game_date, ncaa_gm.game_year, game_fixed, infoID, ncaa_gm_info.gameID, ncaa_gm_info.teamID, ncaa_gm_info.team_score FROM ncaa_gm_info, ncaa_gm WHERE ncaa_gm_info.gameID = ncaa_gm.gameID AND game_year = " . $_GET['year'] . " AND game_fixed = 1 ORDER BY ncaa_gm_info.infoID ASC" ) or die ( $conn->ErrorMsg() );

while ( ! $weighted_rs->EOF ) {

// TEAM 1 INFO
$team_rs = $conn->Execute ( "SELECT teamID, team_gp, team_mpi, team_ww FROM ncaa_tm WHERE teamID = " . $weighted_rs->Fields("teamID") . "" ) or die ( $conn->ErrorMsg() );
$team = $team_rs->Fields("teamID");
$team_gp = $team_rs->Fields("team_gp");
$team_ww = $team_rs->Fields("team_ww");

$opp_rs = $conn->Execute ( "SELECT ncaa_tm.teamID, team_gp, team_mpi, team_ww, ncaa_gm_info.teamID, gameID, team_score FROM ncaa_gm_info, ncaa_tm WHERE gameID = " . $weighted_rs->Fields("gameID") . " AND ncaa_gm_info.teamID != " . $weighted_rs->Fields("teamID") . " AND ncaa_gm_info.teamID = ncaa_tm.teamID" ) or die ( $conn->ErrorMsg() );
$opp_gp = $opp_rs->Fields("team_gp");
$opp_mpi = $opp_rs->Fields("team_mpi");

// IF TEAM WON
if ( $weighted_rs->Fields("team_score") > $opp_rs->Fields("team_score") )  {

	$weighted_win = $team_ww + $opp_mpi + 1;

	$sql = "UPDATE ncaa_tm SET team_ww = $weighted_win WHERE teamID = $team";
	$add_weighted_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );
}

// IF TEAM LOST
if ( $weighted_rs->Fields("team_score") < $opp_rs->Fields("team_score") )  {

	$weighted_win = $team_ww + $opp_mpi - 1;

	$sql = "UPDATE ncaa_tm SET team_ww = $weighted_win WHERE teamID = $team";
	$add_weighted_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );
}

$weighted_rs->MoveNext(); }  

[Piranha]

I think that I know what the problem is. You do 3 queries for each result, that is a total of about 11400 queries per year. Each query take a little time to handle, so it will unfortunatley time out.

Unfortunatley I don't have time to look at it at the moment. I will try to look at it tomorrow to see if it is possible to solve the problem.

[johanafm]

For a good database structure, you should normalize them to BCNF (boyce-codd normal form). Web search for normalization, 3NF and BCNF should give some info on this. once you know what the various normal forms implies and the reasons for them you could decide to choose a lower normal form if there is a very good reason for doing so. Generally, there isn't. By the way, apart from the team_ww which existed in both the ncaa_gm_info and ncaa_tm tables, it seemed to be ok. But it seems you've changed your design since. How does it look now?

For a small change of code that preserves your current queries, use prepared statements for the queries in your loop. Preparing the statement is of course done outside the loop. It should shave off some time as currently on each iteration the query needs to be reevaluated whereas with prepared statements this would be done on each iteration.

[Piranha]

I have had a look now, and I can't think of a good way to solve the problem. I was trying to make it all in one query, but could not find the right way. I still think it is possible to solve with one query, but it takes someone with better database skills than me to solve it.

[johanafm]

To retrieve all data in one query, the following should work.

From the originally posted table structure:

[b]ncaa_gm		ncaa_gm_info	ncaa_tm[/b]
[u]gameID[/u]		[u]infoID[/u]		[u]teamID[/u]
game_date	gameID (fk)	team_name
game_fixed	teamID (fk)	team_rating
		team_score	team_ww

I would first remove infoID from ncaa_gm_info as it seems to me that it does not provide any additional information and there allready is a candidate key: (gameID, teamID), thus giving the table:

[b]ncaa_gm_info[/b]
[u]gameID[/u]
[u]teamID[/u]
team_score

Then to retrieve all data in one query:

SELECT gameID, ncaa_gm_info.teamID, team_score, team_rating, team_ww
FROM ncaa_gm_info
	INNER JOIN ncaa_tm ON ncaa_gm_info.teamID = ncaa_tm.teamID
WHERE gameID IN (SELECT gameID FROM ncaa_gm WHERE game_date = $someDate)
ORDER BY gameID ASC, teamID ASC

or

SELECT ncaa_gm.gameID, ncaa_gm_info.teamID, team_score, team_rating, team_ww
FROM ncaa_gm
	INNER JOIN ncaa_gm_info ON ncaa_gm.gameID = ncaa_gm_info.gameID
	INNER JOIN ncaa_tm ON ncaa_gm_info.teamID = ncaa_tm.teamID
WHERE game_date = $someDate
ORDER BY gameID ASC, teamID ASC

They will give you the same end result, but I have no idea if one will outperform the other for you.

Also, in the future I believe you should stick to using WHERE to limit your queries and JOIN clauses for performing joins. In other words, don't use

WHERE some_table.some_field = some_other_table.some_field 

when you're after an INNER JOIN.