Very slow performance over network on a SQL that should take less than 1 second

[ThunderAI]

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]

[complex05]

is it the query that's taking the time or the actual script? Try outputting the SQL execution time to isolate the delay.

[ThunderAI]

It looks like it is the way I am formating the result.  If I print just the information from the query without any style it takes no time at all to print, but if I add the styling it takes much longer.  Any clues as to how to fix that problem?

[quote author=complex05 link=topic=118751.msg485525#msg485525 date=1166203994]
is it the query that's taking the time or the actual script? Try outputting the SQL execution time to isolate the delay.
[/quote]

[complex05]

Instead of putting so much styling, make use of CSS, and define everything as a class. That should free up about 50% of the coding you have listed above.

[ThunderAI]

most of it is already a class in CSS, at least the style is.  The problem seems to be how the information is pulled and placed in HTML makeup language.

For example:

if I just do this

[code]while *#(YROFSJFJKFS {
?>
<?echo $newarray['fieldb'];?><?echo $newarray['fieldc'];?><?echo $newarray['fieldc'];?>
<?
}[/code]

it is insanly fast, but if I do any styleing to it like

[code]while *#(YROFSJFJKFS {
?>
<ui><li><?echo $newarray['fieldb'];?><?echo $newarray['fieldc'];?><?echo $newarray['fieldc'];?></li>>/ui>
<?
}[/code]

it slows down considerably, and if I put it in a table, hell I better go get a drink and wait for it to timeout.

[quote author=complex05 link=topic=118751.msg485690#msg485690 date=1166215769]
Instead of putting so much styling, make use of CSS, and define everything as a class. That should free up about 50% of the coding you have listed above.
[/quote]