PROCEDURE query slowness problem

0

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)

    
asked by anonymous 11.07.2017 / 15:43

0 answers