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?
– Jefferson Quesado
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
– Tafarel Brayan
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
– Jefferson Quesado
and how to resolve this?
– Tafarel Brayan
Have you ever tried another trial? One that suddenly just print "hello, doc"?
– Jefferson Quesado
@Jeffersonquesado, I just did the test. I created another project with the following sql: SELECT 1 the orga_name, and worked perfectly
– Tafarel Brayan
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!
– Rafael Salomão