I currently have IIS on a Windows 2003 SBE server with PHP 5.2.0 running as an ISAPI module with MySQL 5.24. While running the the code supplied on the localhost the performance is lightning quick as it hsould be, but when using the code over the network or the internet it can take up to 35 to 40 seconds for the entire SQL statement to be completed. Now I realize I am not gods gift to programming in PHP, but I think the code is pretty good. Essentially the code is trying to take user premissions and aviable menu items and creating a dynamic multi-teir menu system ussing CSS. The SQL looks for the main menu items and for each main menu item finds the 1st, 2nd, 3rd, and 4th layers for each. Each SQL statement takes roughply .00018 seconds to conduct in phpMyAdmin, and depending on the total number of results returned the total SQL time should not take more then 1 second. Here is the code in all of its perhaps, badly, programmed glory. [code]function loadnavmenu() { $tmpstarttime = time(); //echo $tmpstarttime; ?> <? // set a variable to null; $tmpnull = "200"; $tmpmenuitemidl1 = ""; $tmpmenuitemidl2 = ""; $tmpmenuitemidl3 = ""; $tmpmenuitemidl4 = ""; //get current user id and place it into a temporary variable $tmpuserid = $_SESSION["user_id"]; // start main code for navigational control and menu system // start layer 1 search, look for menu items that are not slaved to any other menu item. $layer1menuconn = mysqli_connect("localhost", "***USER***", "***PASSWORD***", "***DB***"); if (mysqli_connect_errno()) { // there was an error trying to connect to the mysql database printf("connect failed: %s\n", mysqli_connect_error()); exit(); } else { // load sql syntax with search criteria $sql = "SELECT tbl_systemusers.emp_record_id, tbl_systemusers_ncga.navigational_user_id_cb_int, tbl_systemusers_ncga.navigational_group_id_cb_int, tbl_navigational_control.menu_item_id, tbl_navigational_control.menu_item_location, tbl_navigational_control.menu_item_slaved_to_id, tbl_navigational_control.menu_item_name_long, tbl_navigational_control.menu_item_name_short, tbl_navigational_control.menu_item_archived_yn, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g_a.navigational_archived_yn, tbl_navigational_control_g_a.navigational_groups_id_cb_int, tbl_navigational_control_g_a.navigational_control_id_cb_int FROM tbl_systemusers INNER JOIN tbl_systemusers_ncga ON tbl_systemusers.emp_record_id = tbl_systemusers_ncga.navigational_user_id_cb_int INNER JOIN tbl_navigational_control_g ON tbl_navigational_control_g.navigational_groups_id = tbl_systemusers_ncga.navigational_group_id_cb_int INNER JOIN tbl_navigational_control_g_a ON tbl_navigational_control_g_a.navigational_groups_id_cb_int = tbl_navigational_control_g.navigational_groups_id INNER JOIN tbl_navigational_control ON tbl_navigational_control.menu_item_id = tbl_navigational_control_g_a.navigational_control_id_cb_int WHERE tbl_systemusers.emp_record_id = '".$tmpuserid."' and tbl_navigational_control.menu_item_slaved_to_id = '".$tmpnull."' and tbl_navigational_control.menu_item_name_long <> '***USER***' and tbl_navigational_control.menu_item_archived_yn = 0 AND tbl_navigational_control_g_a.navigational_archived_yn = 0 ORDER BY tbl_navigational_control.menu_item_name_short asc"; $layer1menures = mysqli_query($layer1menuconn, $sql); if ($layer1menures) { ?> <div class="menu"> <ul> <? // put the number of rows found into a new variable $number_of_rows = mysqli_num_rows($layer1menures); //printf("result set has %d rows. \n", $number_of_rows); while ($layer1array = mysqli_fetch_array($layer1menures, MYSQLI_ASSOC)) { ob_flush(); flush(); $tmpfirstnamel1 = $layer1array['emp_firstname']; $tmplastnamel1 = $layer1array['emp_lastname']; $tmpmenuitemlocl1 = $layer1array['menu_item_location']; $tmpuseridl1 = $layer1array['emp_record_id']; $tmpmenuslaveidl1 = $layer1array['menu_item_slaved_to_id']; $tmpmenusshortnl1 = $layer1array['menu_item_name_short']; $tmpmenuitemidl1 = $layer1array['menu_item_id']; ?> <li class="sub" style="margin-top: 0px;"><a href="javascript:call_server_menuitem(<?echo $tmpmenuitemidl1;?>,'<?echo $tmpmenuitemlocl1;?>')" ><?echo $tmpmenusshortnl1;?> <!--[if IE 7]><!--></a><!--<![endif]--> <!--[if lte IE 6]><table><tr><td><![endif]--> <? ob_flush(); flush(); //-----[ start layer 2 menu search]------------------------------------------------- $layer2menuconn = mysqli_connect("localhost", "***USER***", "***PASSWORD***", "***DB***"); if (mysqli_connect_errno()) { // there was an error trying to connect to the mysql database printf("connect failed: %s\n", mysqli_connect_error()); exit(); } else { $sql = "SELECT tbl_systemusers.emp_record_id, tbl_systemusers_ncga.navigational_user_id_cb_int, tbl_systemusers_ncga.navigational_group_id_cb_int, tbl_navigational_control.menu_item_id, tbl_navigational_control.menu_item_location, tbl_navigational_control.menu_item_slaved_to_id, tbl_navigational_control.menu_item_name_long, tbl_navigational_control.menu_item_name_short, tbl_navigational_control.menu_item_archived_yn, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g_a.navigational_archived_yn, tbl_navigational_control_g_a.navigational_groups_id_cb_int, tbl_navigational_control_g_a.navigational_control_id_cb_int FROM tbl_systemusers INNER JOIN tbl_systemusers_ncga ON tbl_systemusers.emp_record_id = tbl_systemusers_ncga.navigational_user_id_cb_int INNER JOIN tbl_navigational_control_g ON tbl_navigational_control_g.navigational_groups_id = tbl_systemusers_ncga.navigational_group_id_cb_int INNER JOIN tbl_navigational_control_g_a ON tbl_navigational_control_g_a.navigational_groups_id_cb_int = tbl_navigational_control_g.navigational_groups_id INNER JOIN tbl_navigational_control ON tbl_navigational_control.menu_item_id = tbl_navigational_control_g_a.navigational_control_id_cb_int WHERE tbl_systemusers.emp_record_id = '".$tmpuserid."' and tbl_navigational_control.menu_item_slaved_to_id = '".$tmpmenuitemidl1."' AND tbl_navigational_control.menu_item_archived_yn = 0 AND tbl_navigational_control_g_a.navigational_archived_yn = 0 ORDER BY tbl_navigational_control.menu_item_name_short asc"; $layer2menures = mysqli_query($layer2menuconn, $sql); if ($layer2menures) { ?> <ul> <? $number_of_rows = mysqli_num_rows($layer2menures); //printf("result set has %d rows. \n", $number_of_rows); while ($layer2array = mysqli_fetch_array($layer2menures, MYSQLI_ASSOC)) { ob_flush(); flush(); $tmpfirstnamel2 = $layer2array['emp_firstname']; $tmplastnamel2 = $layer2array['emp_lastname']; $tmpmenuitemlocl2 = $layer2array['menu_item_location']; $tmpuseridl2 = $layer2array['emp_record_id']; $tmpmenuslaveidl2 = $layer2array['menu_item_slaved_to_id']; $tmpmenusshortnl2 = $layer2array['menu_item_name_short']; $tmpmenuitemidl2 = $layer2array['menu_item_id']; if ($tmpmenuitemlocl2=="unslaved") { ?> <!--maybe--> <li class="menuitem" style="<!--[if lte IE 6]>margin-top:-4px;<![endif]-->"><a href="javascript:call_server_menuitem(<?echo $tmpmenuitemidl2;?>,'<?echo $tmpmenuitemlocl2;?>')" ><?echo $tmpmenusshortnl2;?> <? } else { ?> <!--maybe--> <li class="menuitemselect" style="<!--[if lte IE 6]>margin-top:-3px;<![endif]-->"> <form style="margin: 0px; margin-bottom:0px; margin-top:-1px;" name="menuitem<?echo $tmpmenuitemidl2;?>" method="POST" action="<?echo $tmpmenuitemlocl2;?>" target="layouttableiframecontent"> <input type="hidden" name="menuitemid" value="<?echo $tmpmenuitemidl2;?>"> <a href="#" onclick="javascript:document.menuitem<?echo $tmpmenuitemidl2;?>.submit()"><?echo $tmpmenusshortnl2;?></a> </form> <? } //-----[ start layer 3 menu search]------------------------------------------------- $layer3menuconn = mysqli_connect("localhost", "***USER***", "***PASSWORD***", "***DB***"); if (mysqli_connect_errno()) { // there was an error trying to connect to the mysql database printf("connect failed: %s\n", mysqli_connect_error()); exit(); } else { $sql = "SELECT tbl_systemusers.emp_record_id, tbl_systemusers_ncga.navigational_user_id_cb_int, tbl_systemusers_ncga.navigational_group_id_cb_int, tbl_navigational_control.menu_item_id, tbl_navigational_control.menu_item_location, tbl_navigational_control.menu_item_slaved_to_id, tbl_navigational_control.menu_item_name_long, tbl_navigational_control.menu_item_name_short, tbl_navigational_control.menu_item_archived_yn, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g_a.navigational_archived_yn, tbl_navigational_control_g_a.navigational_groups_id_cb_int, tbl_navigational_control_g_a.navigational_control_id_cb_int FROM tbl_systemusers INNER JOIN tbl_systemusers_ncga ON tbl_systemusers.emp_record_id = tbl_systemusers_ncga.navigational_user_id_cb_int INNER JOIN tbl_navigational_control_g ON tbl_navigational_control_g.navigational_groups_id = tbl_systemusers_ncga.navigational_group_id_cb_int INNER JOIN tbl_navigational_control_g_a ON tbl_navigational_control_g_a.navigational_groups_id_cb_int = tbl_navigational_control_g.navigational_groups_id INNER JOIN tbl_navigational_control ON tbl_navigational_control.menu_item_id = tbl_navigational_control_g_a.navigational_control_id_cb_int WHERE tbl_systemusers.emp_record_id = '".$tmpuserid."' and tbl_navigational_control.menu_item_slaved_to_id = '".$tmpmenuitemidl2."' AND tbl_navigational_control.menu_item_archived_yn = 0 AND tbl_navigational_control_g_a.navigational_archived_yn = 0 ORDER BY tbl_navigational_control.menu_item_name_short asc"; $layer3menures = mysqli_query($layer3menuconn, $sql); if ($layer3menures) { ?> <!--[if IE 7]><!--></a><!--<![endif]--> <!--[if lte IE 6]><table><tr><td><![endif]--> <ul> <? $number_of_rows = mysqli_num_rows($layer3menures); //printf("result set has %d rows. \n", $number_of_rows); while ($layer3array = mysqli_fetch_array($layer3menures, MYSQLI_ASSOC)) { ob_flush(); flush(); $tmpfirstnamel3 = $layer3array['emp_firstname']; $tmplastnamel3 = $layer3array['emp_lastname']; $tmpmenuitemlocl3 = $layer3array['menu_item_location']; $tmpuseridl3 = $layer3array['emp_record_id']; $tmpmenuslaveidl3 = $layer3array['menu_item_slaved_to_id']; $tmpmenusshortnl3 = $layer3array['menu_item_name_short']; $tmpmenuitemidl3 = $layer3array['menu_item_id']; if ($tmpmenuitemlocl3=="unslaved") { ?> <!--maybe--> <li class="menuitem" style="<!--[if lte IE 6]>margin-top:-3px;<![endif]-->"><a href="javascript:call_server_menuitem(<?echo $tmpmenuitemidl3;?>,'<?echo $tmpmenuitemlocl3;?>')" ><?echo $tmpmenusshortnl3;?> <? } else { ?> <!--maybe--> <li class="menuitemselect" style="<!--[if lte IE 6]>margin-top:-3px;<![endif]-->"> <form style="margin: 0px; margin-bottom:0px; margin-top:-1px;" name="menuitem<?echo $tmpmenuitemidl3;?>" method="POST" action="<?echo $tmpmenuitemlocl3;?>" target="layouttableiframecontent"> <input type="hidden" name="menuitemid" value="<?echo $tmpmenuitemidl3;?>"> <a href="#" onclick="javascript:document.menuitem<?echo $tmpmenuitemidl3;?>.submit()"><?echo $tmpmenusshortnl3;?></a> </form> <? } //-----[ start layer 4 menu search]------------------------------------------------- $layer4menuconn = mysqli_connect("localhost", "***USER***", "***PASSWORD***", "***DB***"); if (mysqli_connect_errno()) { // there was an error trying to connect to the mysql database printf("connect failed: %s\n", mysqli_connect_error()); exit(); } else { $sql = "SELECT tbl_systemusers.emp_record_id, tbl_systemusers_ncga.navigational_user_id_cb_int, tbl_systemusers_ncga.navigational_group_id_cb_int, tbl_navigational_control.menu_item_id, tbl_navigational_control.menu_item_location, tbl_navigational_control.menu_item_slaved_to_id, tbl_navigational_control.menu_item_name_long, tbl_navigational_control.menu_item_name_short, tbl_navigational_control.menu_item_archived_yn, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g.navigational_groups_id, tbl_navigational_control_g_a.navigational_archived_yn, tbl_navigational_control_g_a.navigational_groups_id_cb_int, tbl_navigational_control_g_a.navigational_control_id_cb_int FROM tbl_systemusers INNER JOIN tbl_systemusers_ncga ON tbl_systemusers.emp_record_id = tbl_systemusers_ncga.navigational_user_id_cb_int INNER JOIN tbl_navigational_control_g ON tbl_navigational_control_g.navigational_groups_id = tbl_systemusers_ncga.navigational_group_id_cb_int INNER JOIN tbl_navigational_control_g_a ON tbl_navigational_control_g_a.navigational_groups_id_cb_int = tbl_navigational_control_g.navigational_groups_id INNER JOIN tbl_navigational_control ON tbl_navigational_control.menu_item_id = tbl_navigational_control_g_a.navigational_control_id_cb_int WHERE tbl_systemusers.emp_record_id = '".$tmpuserid."' and tbl_navigational_control.menu_item_slaved_to_id = '".$tmpmenuitemidl3."' AND tbl_navigational_control.menu_item_archived_yn = 0 AND tbl_navigational_control_g_a.navigational_archived_yn = 0 ORDER BY tbl_navigational_control.menu_item_name_short asc"; $layer4menures = mysqli_query($layer4menuconn, $sql); if ($layer4menures) { ?> <!--[if IE 7]><!--></a><!--<![endif]--> <!--[if lte IE 6]><table><tr><td><![endif]--> <ul> <? $number_of_rows = mysqli_num_rows($layer3menures); //printf("result set has %d rows. \n", $number_of_rows); while ($layer4array = mysqli_fetch_array($layer4menures, MYSQLI_ASSOC)) { ob_flush(); flush(); $tmpfirstnamel4 = $layer4array['emp_firstname']; $tmplastnamel4 = $layer4array['emp_lastname']; $tmpmenuitemlocl4 = $layer4array['menu_item_location']; $tmpuseridl4 = $layer4array['emp_record_id']; $tmpmenuslaveidl4 = $layer4array['menu_item_slaved_to_id']; $tmpmenusshortnl4 = $layer4array['menu_item_name_short']; $tmpmenuitemidl4 = $layer4array['menu_item_id']; if ($tmpmenuitemlocl4=="unslaved") { ?> <!--maybe--> <li class="menuitem" style="<!--[if lte IE 6]>margin-top:-3px;<![endif]-->"><a href="javascript:call_server_menuitem(<?echo $tmpmenuitemidl4;?>,'<?echo $tmpmenuitemlocl4;?>')" ><?echo $tmpmenusshortnl4;?> <? } else { ?> <!--maybe--> <li class="menuitemselect" style="<!--[if lte IE 6]>margin-top:-3px;<![endif]-->"> <form style="margin: 0px; margin-bottom:0px; margin-top:-1px;" name="menuitem<?echo $tmpmenuitemidl4;?>" method="POST" action="<?echo $tmpmenuitemlocl4;?>" target="layouttableiframecontent"> <input type="hidden" name="menuitemid" value="<?echo $tmpmenuitemidl4;?>"> <a href="#" onclick="javascript:document.menuitem<?echo $tmpmenuitemidl4;?>.submit()"><?echo $tmpmenusshortnl4;?></a> </form> <? } } // end of layer 4 while statement mysqli_free_result($layer4menures); mysqli_close($layer4menuconn); ?> </ul> <? } // end of layer 4 if statement ?> <!--[if lte IE 6]></td></tr></table></a><![endif]--> </li> <? } // end of layer 4 else statement } // end of layer 3 while statement mysqli_free_result($layer3menures); mysqli_close($layer3menuconn); ?> </ul> <? } // end of layer 3 if statement ?> <!--[if lte IE 6]></td></tr></table></a><![endif]--> </li> <? } //-----[ end of layer 3 menu search]------------------------------------------------- } // end of layer 2 while statement mysqli_free_result($layer2menures); mysqli_close($layer2menuconn); ?> </ul> <!--[if lte IE 6]></td></tr></table></a><![endif]--> </li> <? } // end of layer 2 if statement } //-----[ end of layer 2 menu search]------------------------------------------------- } // end of the while loop for layer 1 menu structure mysqli_free_result($layer1menures); mysqli_close($layer1menuconn); ?> </ul> </div> <? } } $tmpendtime = time(); //echo $tmpendtime; echo ($tmpendtime - $tmpstarttime); }[/code]
