Problem of slow consultation of PROCEDURE

Asked

Viewed 141 times

0

There is in my system an email sending routine. Where a bat windows executes a php file that sends the same.

The problem is in a stored Procedure, that executed directly by the bank, brings the result very quickly (question of 0,070 sec), but when run by the system, it takes time to return the result and falls into a FATAL TIME ERROR Exceeded.

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 do in my code, but where the slowness occurs, it is only in the past CALL sp_DocumentosOrgao_Selecionar($param)

  • And how is this trial? Another trial runs slowly too? How many documents does it execute before it fails for exceeded time?

  • The Procedure is very complex, but if I run by the bank, it returns very fast, in the first round of the loop it already takes, and already causes the FATAL ERROR

  • On SQL Server, using JDBC, it sometimes misses some optimizations that occur while running on SQL Server Management Studio. In this case, it has already happened that the same Procedure running via JDBC gives data conversion error, but running via SQL Server Management Studio works well. Sometimes some interactions don’t occur the way we expect

  • and how to resolve this?

  • Have you ever tried another trial? One that suddenly just print "hello, doc"?

  • @Jeffersonquesado, I just did the test. I created another project with the following sql: SELECT 1 the orga_name, and worked perfectly

  • You will need to post the trial to try to find out! It doesn’t seem like a mistake in this code! It is necessary to walk the logical path to find the bottleneck!

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.