Stored procedures are bad, they're often slow and approximately as efficient as ordinary client side code.
[The speedup is usually due to the way the client and stored procedure interface is designed and the way transactions are written as short, focused bursts of SQL.]
Stored procedures are one of the worst places to put code. It breaks your application into two languages and platforms according to rules that are often random.
[This question will be downvoted to have a score of about -30 because many, many people feel that stored procedures have magical powers and must be used in spite of the problems they cause.]
Moving all the stored procedure code to the client will make things much easier for everyone.
You'll still have to update the schema and ORM model from time to time. However, schema changes are isolated from ORM changes, allowing some independence between applications and database schema.
You will be able to test, fix, maintain, understand and adapt all those stored procedures as you rewrite them. Your app will run about the same and become much less fragile because you're no longer breaking into two different technologies.
ORM's are not magic, and good database design skills are absolutely essential to making it work.
Also, programs with a lot of client SQL can become slow because of poor thinking about transaction boundaries. One of the reasons stored procedures appear to be fast is that stored procedures force very, very careful design of transactions.
ORM's don't magically force careful transaction design. Transaction design still has to be done just as carefully as it was when writing stored procedures.
Unfortunately I am not using the Zend Framework but I did good experiences using a dedicated class for the stored procedure stuff which handles the parameters, do the queries and is dealing with the results.
My Model extends the class SPData and adds the methods according to the given SPs.
PHP PDO with MS SQL Drivers are used to call the available stored procedures.
Example using a SP to get an array as a result:
public function getTransactionList($intLimit=0){
$intUserId = $this->getCurrentUserId();
if (false == $intUserId) return false;
$strStatement = '{? = call web.usp_GetTransactionList(?,?)}';
$this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
$this->setParam($intUserId);
$this->setParam($intLimit);
$arr = $this->doCall($strStatement);
return $arr;
}
Example using a SP to get a speciefed output parameter as a result:
public function getBalance(){
$intUserId = $this->getCurrentUserId();
if (false == $intUserId) return false;
$strStatement = '{? = call web.usp_GetBalance(?,?)}';
$this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
$this->setParam($intUserId);
$this->setParam(null, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 30);
$this->doCall($strStatement);
$arrParam = $this->getParam(3);
return (float)$arrParam['value'];
}
Source code of the SPData Class:
class SPData {
protected $con=false;
protected $arrParams=array();
protected $arrParamsBackup=array();
protected $intColCount = 0;
protected $rs=array();
protected function getConnection() {
if (false == isset($GLOBALS['dbcon']))
try {
$GLOBALS['dbcon'] = new PDO('sqlsrv:Server='.DB_SERVER.';database='.DB_DB, DB_USER, DB_PASS, array('ConnectionPooling' => 0));
} catch (PDOException $e) {
die('Connection failed: '.$e->getMessage());
}
return $GLOBALS['dbcon'];
}
protected function resetParams(){
$this->arrParams=array();
}
protected function backupParams(){
$this->arrParamsBackup = $this->arrParams;
}
public function setParam($strValue, $intParams=PDO::PARAM_INT, $intLength=null){
$intIdx = count($this->arrParams);
$intIdx++;
if ($intLength == null)
$this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams);
else
$this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams, 'length'=>$intLength);
}
public function getParam($intIdx){ // from backup array!
return $this->arrParamsBackup[$intIdx];
}
public function doCall($strStatement){
if (false == $this->con) $this->con = $this->getConnection();
$stmt = $this->con->prepare($strStatement);
foreach ($this->arrParams as $key => $value){
if (isset($value['length'])) $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params'],$this->arrParams[$key]['length']);
else $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params']);
}
$stmt->execute();
$arrReturn = $stmt->fetchAll(PDO::FETCH_ASSOC);
$this->intColCount = count($arrReturn);
$stmt->closeCursor();
$this->backupParams(); // backup for getting output params
$this->resetParams();
unset($stmt);
return $arrReturn;
}
public function getColCount(){
return $this->intColCount;
}
}
Best Answer
No, you don't need to worry about a problem that isn't occurring. If your query isn't timing out or running unacceptably slow, you can allocate your time elsewhere.
If you are allowing very complex queries, the question of why you are not allowing slightly sanitized SQL arises. And if you allow arbitrary select statements, then how you handle timeouts and errors and cancellations becomes more important than the performance of your gui-sourced sql.
I've seen and worked with web apps that don't allow direct sql even for competent users, and the results have always been disappointing. Empowerment lets you handle the edge cases from a design perspective, and focus your attention on where it can get the most benefit instead of doing a user's work for them all the time.