Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

How Can I solve the long query time in vmware application?

Im working on my web application in  the ubutnto dev -vmware

Im trying to run a query that is working with a large db in one of the scripts I testes the query in phpmyadmin and saw that the time to execute the query in phpmyadmin is around 4.5 second

In the script its running in a while loop around 600 times and its seem like the whole application is stuck while trying to do  it (basically it will only have to run once properly and update the db and after it will not be such a large portion)

my question is am I just need to give it a little time to run (not so little around 40 min even though the time in the application will be diffrent than phpmyadmin, but its an estimate)

or the applicationserver is timing it out every time? and the application is stuck

Thank you!
0
Nura111
Asked:
Nura111
  • 13
  • 8
  • 3
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Can you share the code or is it of a confidential nature?
If the latter, then you can use EXPLAIN and post the results. Here is an explanation as well as some other very helpful tips: 3 Ways to Speed Up MySQL

If there are not obvious issues to fix, then you can probably just wait it out since this is a one-time deal; however, I suspect there may be another way to do this over WHILE loop. But would need to see the code to really tell for sure.
0
 
Ray PaseurCommented:
Make sure you have indexes on every column used in WHERE, ORDER, GROUP and JOIN clauses.  Make sure you have LIMIT clauses if at all possible.  Never, ever use SELECT *  -- you only want to select the columns you actually need.  Avoid using wildcards and the LIKE clause.  Consider using temporary tables to "downselect" if there is a lot of computation to be done.

Those are just general guidelines, but they cover a lot of the problems that can make MySQL slow.
0
 
Kevin CrossChief Technology OfficerCommented:
Good summary, Ray! gr8gonzo covers a bit of that in his article and running EXPLAIN should help you find where you indexing is not correct if it is not obvious.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Ray PaseurCommented:
I had forgotten about gr8gonzo's article, but it's really excellent.  Look it up!
0
 
Nura111Author Commented:
ok the thing is this same query is working fine in my real environment and also in a diffrent script in the web application  in the dev envi  so I think somehting else is going on there and that the problem is not in the big table MASTER CITY
I attached the code:
so whats happen first I get an error


what happen in I get an error :Server Query Error: INSERT to displayMetro

So I tried to comment the insert section (in the foreach)  to see what happen and than its stuck and do something else that block every other operation tring to work woth the web application I get an error:

There has been an error
Database Statement Error

and I cant access to phpmyadmin also Unless I restart the enviroment.
//check if the domain is nationwide -in that case no metro --	$row - a row from Domain Table
$radius = 1;	
	if 	($row['nationwide']==1){
			$primeCity="---";
			$tdString = "";
	}//no metro assigned
	else if ($metro_id == "none" ){$primeCity="none";}   //strlen($row ['custom_metro']);}

//already set to a custom_metro
	else if (strlen($row ['custom_metro']) !== 0) {$primeCity = $row['custom_metro'];}
    
	else{
		$dis_metro_que = "SELECT metro_name,population FROM displayMetro WHERE domain_id='{$row['id']}' AND prime_metro = 1"; 
		if (($result_dis_metro = $sql->Query($dis_metro_que,$_SERVER['PHP_SELF'])) === FALSE)
			die(ShowError("Server Query Error"));

//check if the domain info is not  already in displayMetro table 
		
		if ($result_dis_metro->num_rows == 0 ){
		//$primeCity="nurit";
			$sql2 = new MySQLConnection();
		    $sql2->SetConnection(DB_HOST, DB_USER, DB_PASSWORD, "MasterCity");
			if (!$sql2->Connect())
			die(ShowError("Server Query Error--whyyy"));

			$_SESSION['displayMetro'] = array();

			// Get the center zip's lat and lon
			 if (($result_master=$sql2->Query("SELECT Latitude,Longitude FROM `MasterCity`.`Cities` WHERE `ZipCode` = '$zip' LIMIT 1")) === FALSE)
					die(ShowError("Server Query Error for MasterCity"));
				
			 if ($result_master->num_rows == 0)
					 die(ShowError("Center Zipcode Not Found"));
			
			 $row_dis_metro = $result_master->fetch_assoc();
			 $lat =  $row_dis_metro['Latitude'];
			 $lon =  $row_dis_metro['Longitude'];
			 $radiusKm = sprintf("%.3f", ($radius+1) * 1.609344);
			$primeCity= "test";
				// Get the basic city information";
				 $query = "SELECT `City`,`State`,`ZipCode`,GET_DISTANCE($lat,$lon,Latitude,Longitude) AS `Distance`, `legacy_city_size` FROM `MasterCity`.`Cities` WHERE GET_DISTANCE($lat,$lon,Latitude,Longitude) <= '$radiusKm'";
				 $query .="GROUP BY `City`,`State`";
				 if (($cities_result=$sql2->Query($query)) === FALSE)
				   die(ShowError("MySQL Query Error its getDisntace")); 

//the loop will go over MasterCity  table and create an array{in $_SESSION['$displayMetro']} of the cities in the radious given desc by population
				 while ($row_dis_metro = $cities_result->fetch_assoc()){
					 // Get the population of the city/state pair
					$query = "SELECT SUM(PopulationEstimate) AS `TotalPop` FROM `Cities` WHERE `City` = '{$row_dis_metro['City']}' AND `State` = '{$row_dis_metro['State']}'";

				    if (($result2=$sql2->Query($query)) === FALSE)
					   die(ShowError("MySQL Query Error 3"));

					if ($result2->num_rows == 0)
					     die(ShowError("TotalPop returned 0")); 

				    $ext_row = $result2->fetch_assoc();
					$miles = (int)($row_dis_metro['Distance'] * 0.621371192);
				    $city = $row_dis_metro['City']; //$row
					//echo $city
	     			array_push($_SESSION['displayMetro'],array('domain_id' => $row['id'], 'metro_name'=>$city , 'population' => $ext_row['TotalPop'], 'distance' => $miles,  'prime_metro' => 0));
					//Print_r($_SESSION['displayMetro']);
//break;

			
				}//while
				


					// Sort the array by population Desc
					uksort($_SESSION['displayMetro'],"cmp");
	//	Insert the table dispaly metro
				$c = 0;
				foreach ($_SESSION['displayMetro'] as $m) {
				$c++;
					if ($c == 1 ){$m['prime_metro'] = 1; $prime_city = $m['metro_name'];}
					if ($c == 4) { break; }	
					$insert_query  = "INSERT INTO displayMetro(domain_id, metro_name, population, distance, prime_metro)";
				   $insert_query .= "VALUES('{$m['domain_id']}', '{$m['metro_name']}', '{$m['population']}', '{$m['distance']}', '{$m['prime_metro']})'";
				
				//	if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)
				//	die(ShowError("Server Query Error: INSERT to displayMetro".$_SESSION['displayMetro']));
				}	
	}////domain info is in displayMetro
	else{
		//$row_dis_metro = $result_dis_metro->fetch_assoc();
		//$primeCity =  $row_dis_metro['metro_name'];
		$primeCity ="nu";
	}
		
}//else

Open in new window

0
 
Nura111Author Commented:
All the indo in $row[field] is coming from a diffrent place in the application and its ok
0
 
Ray PaseurCommented:
Can you please step back from the technical details and describe in plain language what you are trying to do?  There may be a good design pattern that addresses the application.  Thanks, ~Ray
0
 
Nura111Author Commented:
not really because I need this one to work its probably a small thing that I missed I dont want to change this whole thing its a part of a big application and I cant start explain all of it ..

Did you see something in the code that you thing might be causing it?
Thank you!
0
 
Nura111Author Commented:
OK after a few more troubleshotting the problem is in when tryihng to excute the Insert.
Its die.

 so its either this table or the array $_SESSION['display'] or something in the table displayMetro Its currently a new table I created Its working when Im Inserting rows throw phpmyadmin,

Cant figure it out.. Any Ideas?


if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)

					die(ShowError("Server Query Error: INSERT to displayMetro".$_SESSION['displayMetro']));

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I'll defer to Ray on PHP side of things. If you find this is database related, I will chime back in.
0
 
Nura111Author Commented:
Ok I got it !! its working Its just take a long time like 30 or eo min maybe it will be faster on the real server

Ray-I dont know design pattern but if you have any suggestion to iuse it Ill be happy to hear them and learn about it
0
 
Ray PaseurCommented:
Well, the canonical work on Design Patterns is by Martin Fowler, and it is a semester of the engineering curriculum in college.  But you don't need to get into all of that.  We know the book on design patterns cold.  All we need is your simple explanation of the objectives.  Something like, "I am trying to show the client the nearest store."

Fowler is here:
http://www.amazon.com/gp/product/0321127420?ie=UTF8&tag=martinfowlerc-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0321127420
0
 
Nura111Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Nura111's comment http:/Q_27289563.html#36476538

for the following reason:

The great helpful suggestion was not the answer this time I debuged it and got to the problem myself
0
 
Nura111Author Commented:
I still have a weird problem as I mentioned before at the first time the script is running and need to perform the long query and the db update its take a lot of time and  also Its seem like that only when i refresh the application page its adding only one result at a time no matter how time as passes I found that weird
any Idea?
0
 
Ray PaseurCommented:
Please post the solution you found so others in the EE community can learn from your experience, thanks. ~Ray
0
 
Nura111Author Commented:
RAY-

Oh sorry didnt notice I didnt attched it It was a syntax issue:
the right line is  $insert_query .= "VALUES('{$m['domain_id']}', '{$m['metro_name']}', '{$m['population']}', '{$m['distance']}', '{$m['prime_metro']}')

a stupid issue :(

Do you mind take a look at the code again the table update section is causing a problem from some reason it it stop after every one time and than I have to refresh the application so it continue maybe its the break; ? but isnt it just suppose to stop the for each loop?
I can also open a new question or somehow transfer points for this question.
//	Insert the table dispaly metro

				$countIt = 0;

		     	foreach ($_SESSION['displayMetro'] as $m) {

				$countIt++;

				if ($countIt == 1 ){$m['prime_metro'] = 1; $prime_city = $m['metro_name'];}

					if ($countIt == 4) { break; }	

					$insert_query  = "INSERT INTO displayMetro(domain_id, metro_name, population, distance, prime_metro)";

				   $insert_query .= "VALUES('{$m['domain_id']}', '{$m['metro_name']}', '{$m['population']}', '{$m['distance']}', '{$m['prime_metro']}')";

				

					if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)

					die(ShowError("Server Query Error: INSERT to displayMetro".$_SESSION['displayMetro']));

				}

Open in new window

0
 
Ray PaseurCommented:
Not sure what could be wrong, since I can't see the data.  You might try printing out the values in $_SESSION['displayMetro'] and $insert_query to see if they are what you expect.
0
 
Nura111Author Commented:
Ray- the insert to the the db its causing that:
so if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)
is stoping the script every iteration
0
 
Nura111Author Commented:
they are what I expect I cahecked in the displayMetro table where im adding the data.  
0
 
Nura111Author Commented:
Ray come back :(
0
 
Ray PaseurCommented:
Back... but we are in a flood zone now so I may be away from the computer for a while.

What does the $sql->Query() method expect for input?
0
 
Nura111Author Commented:
HI!!  first of all Thank you for coming back, I kind of drag back to this question again do you can take a look for more info. (its pretty much the same info I gave here I think)
I attached the function.
http://www.experts-exchange.com/Database/MySQL/Q_27298847.html


function Query($query,$logEntry="-no log entry-")
    {
        if (! $this->Connect())
            return FALSE;

        if (! $this->mysqli->real_query($query)) {
            $msg = "MySQL error: $logEntry\n$query\n" . $this->mysqli->error;

            // Log the error message to the log file
            if ($fp = @fopen($this->logPath, "a")) {
                $ts = date('m/d/Y g:i:s A', time());
                fwrite($fp, "$ts\n$msg\n\n");
                fclose($fp);
            }

            return FALSE;
        }

Open in new window

0
 
Nura111Author Commented:
Ok I found the problem it was that $result was used previous and the new value changed the previous and stoped the while loop/
I keep having stupid problkem from that soet what making me think maybe I should chnage my dev enviroment I currently develop using notpead++ and vmware to check my application (but no specified editor there)
and xmapp to test error code.
any idea? I can ask a new question if you want.
Thank you for all the help
0
 
Ray PaseurCommented:
I use either textpad or notepad++.  I develop on my desktop computer.  I use FTP to put my code into the hosting environment where I can test it in an environment that is as close as possible to the environment in which it will run.  This works well for me, mostly because I have built up habits that fit my own style of programming.

I know that some of my colleagues like Eclipse.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 13
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now