I have a facebook app that I have written in PHP/MYSQL and FBJS/AJAX. The app is Rails Across Europe, which may be found at http://apps.facebook.com/rails_across_europe (please note that it does require access to your FB data). If you would like to view a screencast of the app's operation, you may do so at http://screenr.com/TZR and http://screenr.com/sZR (each screencast is ~3 minutes)
The problem is that response to user input is very slow. I'm not sure what could be causing this performance bottleneck. I don't have any experience with performance optimization, which is why I'm asking your help. I figure the bottleneck could be any of the following areas:
- MySQL
- AJAX
- Web server
Regarding my web hosting, I am using an A2 Hosting shared-hosting account. I'm not sure whether this type of web host is capable of handling the kind of intensive database and user activity that my app requires, but it's all I can afford. If this could be the source of my performance problem, please let me know.
To help determine the source of the performance problem, I am including some source code. The first is a SQL query, the second is an AJAX call.
Train movement seems to be the slowest operation, so I'll use that as an example. I realize that this is an extremely long code sample, but unless I submit the entire context for your review, I don't know how anyone could help me. Here is the PHP/MYSQL code:
MoveTrain.php
public function moveTrain($destCityId) {
require_once 'Train.php';
$trainModel = new Train();
require_once 'Route.php';
$routeModel = new Route();
$userNamespace = new Zend_Session_Namespace('User');
$gamePlayerId = $userNamespace->gamePlayerId;
$trainData = $trainModel->getTrain($gamePlayerId);
$originCityId = $trainData['origin_city_id'];
$destChanged = false;
if ( $destCityId != $trainData['dest_city_id'] ) {
$originCityId = $trainData['dest_city_id'];
$destChanged = true;
}
$routeResp = $routeModel->getPlayerRouteIdByCityIds($gamePlayerId, $originCityId, $destCityId);
$routeId = $routeResp['route_id'];
$trainRow = array();
// if route is invalid OR if destination city has not changed and train has arrived,
// bypass train movement
if($routeResp['error_msg'] == 'SUCCESS' || ($routeResp['error_msg'] == 'ROUTE_NOT_OWNED' && !$destChanged)) {
if((!$destChanged && $trainData['status'] == 'ENROUTE') ||
($destChanged && $trainData['status'] == 'ARRIVED')) {
$routeData = $routeModel->getRouteByCityIds($originCityId, $destCityId);
$unitsToDestination = 0;
$trainRow['direction'] = $routeModel->getRouteTravelDirection($originCityId, $destCityId); //+
// if traveling to a new city destination and traveling negative then train track unit
// is equal to route track unit count (because train is at the end of a new route)
switch ($trainRow['direction']) {
case '+':
if($destChanged && $trainData['status'] == 'ARRIVED') {
$trainData['track_unit'] = 0;
}
$unitsToDestination = $routeData['track_unit_count'] - $trainData['track_unit'];
break;
case '-':
if($destChanged && $trainData['status'] == 'ARRIVED') {
$trainData['track_unit'] = $routeData['track_unit_count'];
}
$unitsToDestination = $trainData['track_unit'];
break;
default:
break;
}
// 2. Move the train
$unitsToDestination = $unitsToDestination - $trainData['track_units_remaining'];
$trackUnitsRemaining = 0;
$trainArrived = false;
// Note that I changed from < to <=
if ( $unitsToDestination <= 0) {
// Went too far or arrived.
$trackUnitsRemaining = abs($unitsToDestination);
$trackUnit = $routeData['track_unit_count'];
if($trainRow['direction'] == '-') {
$trackUnit = 0;
}
$trainArrived = true;
} else {
// Did not reach destination
$trackUnitsRemaining = 0;
switch ( $trainRow['direction'] ) {
case '+':
$trackUnit = $routeData['track_unit_count'] - $unitsToDestination;
break;
case '-':
$trackUnit = $unitsToDestination;
break;
default:
break;
}
}
// 3. Save changes carefully.
$trainRow['route_id'] = $routeId;
$trainRow['origin_city_id'] = $originCityId;
$trainRow['dest_city_id'] = $destCityId;
$trainRow['track_unit'] = $trackUnit; //5
$trainRow['track_units_remaining'] = $trackUnitsRemaining; //2
// $trainArrived = ($trackUnit == 0 || $trackUnit == $routeData['track_unit_count']);
$trainRow['status'] = ($trainArrived) ? 'ARRIVED' : 'ENROUTE';
$trainRow['date_last_used'] = date('Y-m-d H:i:s');
//$trainId = $trainModel->getTrainId($gamePlayerId);
$where = $trainModel->getAdapter()->quoteInto('id = ?', $trainData['id']);
$trainModel->update($trainRow, $where);
} else {
$trainRow = $trainData;
}
}
return $trainRow;
}
Train.php:
public function getTrain($gamePlayerId) {
$sql = $this->getAdapter()->quoteInto("SELECT gp.player_number, t.* FROM train t, game_player gp WHERE t.`game_player_id` = ?", $gamePlayerId)
. ' AND gp.id = t.game_player_id;';
$stmt = $this->getAdapter()->query($sql);
$train = $stmt->fetchAll();
return $train[0];
}
Route.php:
public function getPlayerRouteIdByCityIds($gamePlayerId, $cityId1, $cityId2) {
$resp = array('route_id' => null, 'error_msg' => 'INVALID_ROUTE');
$where = $this->getAdapter()->quoteInto('(city_id_1 = ?', $cityId1)
. $this->getAdapter()->quoteInto(' AND city_id_2 = ?)', $cityId2)
. $this->getAdapter()->quoteInto(' OR (city_id_1 = ?', $cityId2)
. $this->getAdapter()->quoteInto(' AND city_id_2 = ?)', $cityId1);
$select = $this->select()->where($where);
$row = $this->fetchRow($select);
if($row) {
require_once 'PlayerRoute.php';
$playerRouteModel = new PlayerRoute();
if($playerRouteModel->isRouteOwned($row->id, $gamePlayerId)) {
$resp['route_id'] = $row->id;
$resp['error_msg'] = 'SUCCESS';
return $resp;
}
$resp['error_msg'] = 'ROUTE_NOT_OWNED';
}
return $resp;
}
public function getRouteByCityIds($cityId1, $cityId2) {
$db = $this->getAdapter();
$sql = 'SELECT * FROM route AS r'
. $db->quoteInto(' WHERE (r.city_id_1 = ?', $cityId1)
. $db->quoteInto(' AND r.city_id_2 = ?)', $cityId2)
. $db->quoteInto(' OR (r.city_id_1 = ?', $cityId2)
. $db->quoteInto(' AND r.city_id_2 = ?);', $cityId1);
$stmt = $db->query($sql);
$routeRow = array();
foreach($stmt as $row) {
$routeRow = $row;
}
if(count($routeRow) == 0) { return false; }
$sql = $db->quoteInto('SELECT track_unit_count FROM route_count WHERE id = ?', $routeRow['id']);
$stmt = $db->query($sql);
foreach($stmt as $row) {
$routeRow['track_unit_count'] = $row['track_unit_count'];
}
return $routeRow;
}
public function getRouteTravelDirection($startCityId, $endCityId) {
$routeData = $this->getRouteByCityIds($startCityId, $endCityId);
return ($routeData['city_id_1'] == $startCityId) ? '+' : '-';
}
PlayerRoute.php
public function isRouteOwned($routeId, $gamePlayerId) {
$where = $this->_db->quoteInto('route_id = ?', $routeId)
. $this->_db->quoteInto(' AND game_player_id = ?', $gamePlayerId);
$select = $this->select()->where($where);
$row = $this->fetchRow($select);
return (!$row) ? false : true;
}
I decided against submitting the FBJS/AJAX code sample. Maybe I'll submit that as another question. Thanks to anyone who can help me with this.