Databases
StackOverflow2013
Postsdbo
POPHP SQLSRV query very slow
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP SQLSRV query very slow
    text
    copied!<p><strong>UPDATED WITH FULL FUNCTION CODE</strong></p> <p>I'm running PHP on IIS 7.5 to connect to a SQL Server database, hosted in another server...</p> <p>I have my webpage fully working but It takes ages to perform simple querys like this... It returns only (12*3)*n results per loop, the loops are given inside a foreach "servicio_id" where n = the numbers of services I have... A simple page must return me: ((12*3)*14) * 3 results... I'll explain: ((12 * 3) * SERVICES) * NUMBER OF TABLES (Banksphere and Umbrales * 2 like you will see...)</p> <p>I think the problem resides in the tables, because I have almost 2 million rows, and everyday it increases about 20.000 lines... But I think it can't take that long to run the query, because I specify the arguments like entidad_id, servicio_id, peticion_id, fecha... It takes like 20-30 seconds to show me the page, and sometimes it just stops and just show half of it... Some advice please?</p> <pre><code>public static function getValues($entidad_id, $servicio_id, $peticion_id, $fecha) { if(date('d', strtotime($fecha)) &gt;= '28' || date('d', strtotime($fecha)) &lt;= '1') { $dia_id = 8; } else { $dia_id = date('N', strtotime($fecha)) -1; } echo '&lt;script type="text/javascript" language="javascript"&gt; function openwindow(URL) { window.open(URL,"Comentarios","menubar=1,resizable=1,width=700,height=350"); } &lt;/script&gt;'; $conn = sqlsrv_connect(DB_SERVER.', '.DB_PORT, array("UID"=&gt;DB_USER, "PWD"=&gt;DB_PASS, "Database"=&gt;DB_NAME)); $sqlQuery = sqlsrv_query($conn, " SELECT TOP ".OPT_RESULTADOS_MAXIMOS." * FROM Banksphere WHERE entidad_id = '$entidad_id' AND servicio_id = '$servicio_id' AND peticion_id = '$peticion_id' AND fecha = '".$fecha."' ORDER BY hora_id DESC "); while ($row = sqlsrv_fetch_array($sqlQuery)) { $umbralesQuery = sqlsrv_query($conn, " SELECT * FROM Umbrales WHERE entidad_id = '$row[entidad_id]' AND servicio_id = '$row[servicio_id]' AND peticion_id = '$row[peticion_id]' AND dia_id = '$row[dia_id]' AND hora_id = '$row[hora_id]' ORDER BY hora_id DESC "); $umbralesOk = sqlsrv_query($conn, " SELECT * FROM Umbrales WHERE entidad_id = '$row[entidad_id]' AND servicio_id = '$row[servicio_id]' AND peticion_id = '0' AND dia_id = '$row[dia_id]' AND hora_id = '$row[hora_id]' ORDER BY hora_id DESC "); $umbrales = sqlsrv_fetch_array($umbralesQuery); $uOK = sqlsrv_fetch_array($umbralesOk); sqlsrv_free_stmt($umbralesQuery); sqlsrv_free_stmt($umbralesOk); $medias = Umbrales::getValues($uOK['minimo'], $umbrales['minimo']); if($row['usuario'] != '') $postedby = 'Comentado por '.$row['usuario'].': '; else $postedby = ''; if($peticion_id == 0) { if($row['valor'] &lt; $medias[0]) { $color = "D"; } else if($row['valor'] &gt; $medias[1]) { $color = "A"; } else { $color = "OK"; } $minimo = $medias[0]; $maximo = $medias[1]; } if($peticion_id == 1) { if($row['valor'] &gt; $medias[2]) { $color = "D"; } else { $color = "OK"; } $minimo = 0; $maximo = $medias[2]; } if($peticion_id == 2) { if($row['valor'] &gt; $medias[3]) { $color = "D"; } else { $color = "OK"; } $minimo = 0; $maximo = $medias[3]; } if($color == "OK"){ echo ' &lt;td title="MINIMO: '.$minimo.' | MAXIMO: '.$maximo.'" class="'.$color.'"&gt;'.round($row['valor'], 3).'&lt;/td&gt; '; } else { echo ' &lt;td title="MINIMO: '.$minimo.' | MAXIMO: '.$maximo.'" class="'.$color.'"&gt; &lt;a href="javascript:openwindow(\'/includes/commentForm.php?servicio='.$umbrales['servicio_id'].'&amp;umbral_id='.$umbrales['id'].'&amp;id='.$row['id'].'\')"&gt;'.round($row['valor'], 3).'&lt;/a&gt; &lt;/td&gt; '; } } sqlsrv_free_stmt($sqlQuery); sqlsrv_close($conn); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload