PHP SQLSRV query very slow

Question

UPDATED WITH FULL FUNCTION CODE

I'm running PHP on IIS 7.5 to connect to a SQL Server database, hosted in another server...

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...)

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?

public static function getValues($entidad_id, $servicio_id, $peticion_id, $fecha) {

    if(date('d', strtotime($fecha)) >= '28' || date('d', strtotime($fecha)) <= '1') {
        $dia_id = 8;
    } else {
        $dia_id = date('N', strtotime($fecha)) -1;
    }
    echo '<script type="text/javascript" language="javascript"> 
    function openwindow(URL) {
        window.open(URL,"Comentarios","menubar=1,resizable=1,width=700,height=350"); 
    }
    </script>'; 

    $conn = sqlsrv_connect(DB_SERVER.', '.DB_PORT, array("UID"=>DB_USER,  "PWD"=>DB_PASS,  "Database"=>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'] < $medias[0]) {
                $color = "D";
            } else if($row['valor'] > $medias[1]) {
                $color = "A";
            } else {
                $color = "OK";
            }
            $minimo = $medias[0];
            $maximo = $medias[1];
        }
        if($peticion_id == 1) {
            if($row['valor'] > $medias[2]) {
                $color = "D";
            } else {
                $color = "OK";
            }
            $minimo = 0;
            $maximo = $medias[2];
        }
        if($peticion_id == 2) {
            if($row['valor'] > $medias[3]) {
                $color = "D";
            } else {
                $color = "OK";
            }
            $minimo = 0;
            $maximo = $medias[3];
        }           
        if($color == "OK"){
            echo '
            <td title="MINIMO: '.$minimo.' | MAXIMO: '.$maximo.'" class="'.$color.'">'.round($row['valor'], 3).'</td>
            ';
        } else {
            echo '
            <td title="MINIMO: '.$minimo.' | MAXIMO: '.$maximo.'" class="'.$color.'">
                <a href="javascript:openwindow(\'/includes/commentForm.php?servicio='.$umbrales['servicio_id'].'&umbral_id='.$umbrales['id'].'&id='.$row['id'].'\')">'.round($row['valor'], 3).'</a>
            </td>
            ';
        }
    }
    sqlsrv_free_stmt($sqlQuery);
    sqlsrv_close($conn);
}

Answer 1

Got the solution... The speed improvement is acceptable now...

    $sqlQuery = sqlsrv_query($conn, "
        SELECT TOP ".OPT_RESULTADOS_MAXIMOS."
            bs.id AS valor_id,
            bs.valor AS valor,
            bs.servicio_id,
            u.id AS umbral_id,
            u.minimo AS media,
            uok.minimo AS media_ok
        FROM
            [Banksphere] bs
        INNER JOIN
            Umbrales u
        ON
            u.entidad_id = bs.entidad_id
        AND
            u.servicio_id = bs.servicio_id
        AND
            u.dia_id = bs.dia_id
        AND
            u.hora_id = bs.hora_id
        AND
            u.peticion_id = bs.peticion_id
        INNER JOIN
            Umbrales uok
        ON
            uok.entidad_id = bs.entidad_id
        AND
            uok.servicio_id = bs.servicio_id
        AND
            uok.dia_id = bs.dia_id
        AND
            uok.hora_id = bs.hora_id
        AND
            uok.peticion_id = '0'
        WHERE
            bs.entidad_id = '$entidad_id'
        AND
            bs.servicio_id = '$servicio_id'
        AND
            bs.peticion_id = '$peticion_id'
        AND
            bs.fecha = '$fecha'
        ORDER BY
            bs.hora_id DESC,
            bs.peticion_id ASC
    ");

Answer 2

Could you do one query that includes the Umbrales table, on the first query? The way you're doing it you're calling a transaction for every row you get on the first query. The other way you'd only be doing one transaction with the results.