Search the Community

Hey all I am a self-taught AS3/Flex/AIR developer and I have built a system of touchscreen-kiosks running on AIR with remote content managment from an online Flex app. This is the first time I have done server side stuff, so feel free to correct my question if needed. Both the AIR apps and the Flex app connect to a mysql database for simple CRUD operations using php scripts and the Zend framework. The kiosks call the server every 30 seconds to update. All these simple server side php scripts were auto-generated by Flash Builder's data wizard. I did the simple adjusments (gateway.php, config.ini, mysqli connection parameters) and deployed everything to the client's server. This is a a shared-server type. Now, the system works. But it works slowly. Each create/read/update/delete operation works but I think it should be a lot faster. Also, these operations put a lot of load on the server's cpu. According to the hosting guys, this system opens 6 php-cgi proccesses that take up 40% of the server cpu power, and by doing so slows itself down and other sites hosted on that server. My questions are: First of all, is there a problem to begin with or is this performance expexted from the Zend framework? Are the auto-generated scripts written by Flash Builder poorly written and can be optimized? Can this kind of system stay on a shared-hosted-server? Should we move to a VPS server for better performance? Is it posiible that if the tables I created on the mysql database with phpmyadmin weren't optimiatlly built that it would have this kind of impact on performance? I don't know php or mysql. Any kind of help will be much appriciated. Saar Here is the script written by Flash Builder: I havn't added anything - just changed the connection parameters. <?php class KiosksService { var $username = "--------"; var $password = "--------"; var $server = "--------"; var $port = "3306"; var $databasename = "--------"; var $tablename = "kiosks"; var $connection; /** * The constructor initializes the connection to database. Everytime a request is * received by Zend AMF, an instance of the service class is created and then the * requested method is invoked. */ public function __construct() { $this->connection = mysqli_connect( $this->server, $this->username, $this->password, $this->databasename, $this->port ); $this->throwExceptiononerror($this->connection); } /** * Returns all the rows from the table. * * Add authroization or any logical checks for secure access to your data * * @return array */ public function getAllKiosks() { $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename"); $this->throwExceptiononerror(); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); $rows = array(); mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra); while (mysqli_stmt_fetch($stmt)) { $row->Pingdate = new DateTime($row->Pingdate); $rows[] = $row; $row = new stdClass(); mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra); } mysqli_stmt_free_result($stmt); mysqli_close($this->connection); return $rows; } /** * Returns the item corresponding to the value specified for the primary key. * * Add authorization or any logical checks for secure access to your data * * * @return stdClass */ public function getKiosksByID($itemID) { $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename where KioskID=?"); $this->throwExceptiononerror(); mysqli_stmt_bind_param($stmt, 'i', $itemID); $this->throwExceptiononerror(); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra); if(mysqli_stmt_fetch($stmt)) { $row->Pingdate = new DateTime($row->Pingdate); return $row; } else { return null; } } /** * Returns the item corresponding to the value specified for the primary key. * * Add authorization or any logical checks for secure access to your data * * * @return stdClass */ public function createKiosks($item) { $stmt = mysqli_prepare($this->connection, "INSERT INTO $this->tablename (Station, Branch, Chain, Pingdate, Layout, Online, Clips, Extra) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); $this->throwExceptiononerror(); mysqli_stmt_bind_param($stmt, 'sssssisi', $item->Station, $item->Branch, $item->Chain, $item->Pingdate->toString('YYYY-MM-dd HH:mm:ss'), $item->Layout, $item->Online, $item->Clips, $item->Extra); $this->throwExceptiononerror(); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); $autoid = mysqli_stmt_insert_id($stmt); mysqli_stmt_free_result($stmt); mysqli_close($this->connection); return $autoid; } /** * Updates the passed item in the table. * * Add authorization or any logical checks for secure access to your data * * @param stdClass $item * @return void */ public function updateKiosks($item) { $stmt = mysqli_prepare($this->connection, "UPDATE $this->tablename SET Station=?, Branch=?, Chain=?, Pingdate=?, Layout=?, Online=?, Clips=?, Extra=? WHERE KioskID=?"); $this->throwExceptiononerror(); mysqli_stmt_bind_param($stmt, 'sssssisii', $item->Station, $item->Branch, $item->Chain, $item->Pingdate->toString('YYYY-MM-dd HH:mm:ss'), $item->Layout, $item->Online, $item->Clips, $item->Extra, $item->KioskID); $this->throwExceptiononerror(); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); mysqli_stmt_free_result($stmt); mysqli_close($this->connection); } /** * Deletes the item corresponding to the passed primary key value from * the table. * * Add authorization or any logical checks for secure access to your data * * * @return void */ public function deleteKiosks($itemID) { $stmt = mysqli_prepare($this->connection, "DELETE FROM $this->tablename WHERE KioskID = ?"); $this->throwExceptiononerror(); mysqli_stmt_bind_param($stmt, 'i', $itemID); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); mysqli_stmt_free_result($stmt); mysqli_close($this->connection); } /** * Returns the number of rows in the table. * * Add authorization or any logical checks for secure access to your data * * */ public function count() { $stmt = mysqli_prepare($this->connection, "SELECT COUNT(*) AS COUNT FROM $this->tablename"); $this->throwExceptiononerror(); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); mysqli_stmt_bind_result($stmt, $rec_count); $this->throwExceptiononerror(); mysqli_stmt_fetch($stmt); $this->throwExceptiononerror(); mysqli_stmt_free_result($stmt); mysqli_close($this->connection); return $rec_count; } /** * Returns $numItems rows starting from the $startIndex row from the * table. * * Add authorization or any logical checks for secure access to your data * * * * @return array */ public function getKiosks_paged($startIndex, $numItems) { $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename LIMIT ?, ?"); $this->throwExceptiononerror(); mysqli_stmt_bind_param($stmt, 'ii', $startIndex, $numItems); mysqli_stmt_execute($stmt); $this->throwExceptiononerror(); $rows = array(); mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra); while (mysqli_stmt_fetch($stmt)) { $row->Pingdate = new DateTime($row->Pingdate); $rows[] = $row; $row = new stdClass(); mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra); } mysqli_stmt_free_result($stmt); mysqli_close($this->connection); return $rows; } /** * Utility function to throw an exception if an error occurs * while running a mysql command. */ private function throwExceptiononerror($link = null) { if($link == null) { $link = $this->connection; } if(mysqli_error($link)) { $msg = mysqli_errno($link) . ": " . mysqli_error($link); throw new Exception('MySQL Error - '. $msg); } } } ?>