如何改进我的查询 - PHP，MSSQL - 两级水平菜单

Question

I have a parent/child horizontal menu... The first query finds the top level items and then the second query uses that information to loop and query for each set of child items - this query takes too long. How can I speed it up?

 <ul>
   <?php 
     //query top level navigation
     $tsqlnav = "SELECT DISTINCT id_division AS id, id_division_slot AS slot, parent FROM [xxx].[dbo].[service_div] WHERE id_service = ? AND content_languages_list LIKE ? AND (parent ='' or parent is NULL) ORDER BY slot;";
     $paramsnav = array($lang_id_service, '%'.$_SESSION['id_language'].'%');
     $optionsnav =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
     $stmtnav = sqlsrv_query($conn, $tsqlnav, $paramsnav, $optionsnav);
     if($stmtnav === false) {die( print_r( sqlsrv_errors(), true ));}
     $row_countnav = sqlsrv_num_rows( $stmtnav );
     if ($row_countnav === false) {echo "Error in retrieving row count."; }
     elseif ($row_countnav > 0) {
       $div='0';
       while( $row = sqlsrv_fetch_array( $stmtnav, SQLSRV_FETCH_ASSOC) ) {
         $parent = $row['parent'];
         $id_div = $row['id'];
         $id_div_slot = $row['slot'];
         echo "<li>".$row['id'];
            echo "<ul>";
             $tsqlnav2 = "SELECT DISTINCT id_module AS id, id_module_slot AS slot, parent FROM [xxx].[dbo].[service] WHERE id_service = ? AND content_languages_list LIKE ? AND id_division = ? ORDER BY slot;";
             $paramsnav2 = array($lang_id_service, '%'.$_SESSION['id_language'].'%', $id_div);
             $optionsnav2 =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
             $stmtnav2 = sqlsrv_query($conn, $tsqlnav2, $paramsnav2, $optionsnav2);
             if( $stmtnav2 === false ) { die( print_r( sqlsrv_errors(), true )); }
             $row_countnav2 = sqlsrv_num_rows( $stmtnav2 );
             if ($row_countnav2 === false) {echo "Error in retrieving row count."; }
             elseif ($row_countnav2 > 0) { 
               while( $row2 = sqlsrv_fetch_array( $stmtnav2, SQLSRV_FETCH_ASSOC) ) {
                 $id_mod = 'm'.$row2['id']; 
                 echo "<li>".$id_mod."</li>";
               }
             }
           echo "</ul>";
         echo "</li>";  
       }
     }
   ?>
 </ul>

Answer 1

I would suggest to break this logic into two.

1. Get your top level items and create array of all top level items.
2. Run a loop on top level items array and get child item of each top level item one by one.

Right now you are making multiple database requests before closing your initial request. There should be one database call at one time.