There is an email sending routine on my system. Where a bat of windows runs a php file that sends it.
The problem is in a stored procedure , which runs directly by the bank, brings the result very quickly ( 0.070 sec question), but when executed by the system , it takes time to return the result and falls into a FATAL Exceeded Time Error .
Code:
try {
$connSisDoc = new PDO('mysql:host=xxx.xxx.xx;dbname=xxxx', 'xx', 'xxx');
$connSisDoc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo 'ERROR SISDOC: ' . $e->getMessage();
}
//verifico os emails pendentes
$sql = "SELECT * FROM documentos_email WHERE doem_enviado = 'N'";
$stmt = $connSisDoc->prepare($sql);
$stmt->execute();
$docEmail = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($docEmail as $doem) {
//pega email dos usuários
$sql = "SELECT usua_id, usua_nome, usua_email FROM dbacesso.usuario WHERE usua_id = ".$doem->doem_destinatario;
$stmt = $connSisDoc->prepare($sql);
$stmt->execute();
$user = $stmt->fetchAll(PDO::FETCH_OBJ);
//Seta o corpo da mensagem (**o corpo vem de uma função a parte**)
echo bodyMail($doem->doem_destinatario, $connSisDoc, $user[0]->usua_nome, $doem->doem_admin, $doem->doem_empr_id);
}
function bodyMail($destinario, $connSisDoc, $nomeDestinatario, $admin, $empresa){
//Consulto documentos do usuário passado no parametro da função
$documentos = "SELECT * FROM xxx WHERE xxxx";
if($admin == 'N')
$documentos.=" AND doem_destinatario = ". $destinario;
$stmt = $connSisDoc->prepare($documentos);
$stmt->execute();
$docs = $stmt->fetchAll(PDO::FETCH_OBJ);
$stmt->closeCursor();
foreach ($docs as $doc) {
//Aqui que está acontecendo a lentidão no retorno da consulta
$sp = "CALL 'sp_DocumentosOrgao_Selecionar'('".$doc->doco_id."')";
$stmt2 = $connSisDoc->prepare($sp);
$stmt2->execute();
$orgaos = $stmt2->fetchAll(PDO::FETCH_OBJ);
$stmt2->closeCursor();
}
}
I put practically all the queries I make in my code, but where slow occurs, it is only in procedure
CALL sp_DocumentosOrgao_Selecionar($param)