Solved

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

Posted on 2011-09-02
24
293 Views
Last Modified: 2012-05-12
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
Comment
Question by:Nura111
  • 13
  • 8
  • 3
24 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36476054
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
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 350 total points
ID: 36476106
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
ID: 36476158
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36476177
I had forgotten about gr8gonzo's article, but it's really excellent.  Look it up!
0
 

Author Comment

by:Nura111
ID: 36476220
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
 

Author Comment

by:Nura111
ID: 36476225
All the indo in $row[field] is coming from a diffrent place in the application and its ok
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36476340
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
 

Author Comment

by:Nura111
ID: 36476347
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
 

Author Comment

by:Nura111
ID: 36476538
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36476540
I'll defer to Ray on PHP side of things. If you find this is database related, I will chime back in.
0
 

Author Comment

by:Nura111
ID: 36476548
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36476634
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
 

Author Comment

by:Nura111
ID: 36490358
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
 

Author Comment

by:Nura111
ID: 36490267
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36490359
Please post the solution you found so others in the EE community can learn from your experience, thanks. ~Ray
0
 

Author Comment

by:Nura111
ID: 36490574
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36490608
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
 

Author Comment

by:Nura111
ID: 36490621
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
 

Author Comment

by:Nura111
ID: 36490632
they are what I expect I cahecked in the displayMetro table where im adding the data.  
0
 

Author Comment

by:Nura111
ID: 36491018
Ray come back :(
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36506862
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
 

Author Comment

by:Nura111
ID: 36512362
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
 

Author Comment

by:Nura111
ID: 36513530
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36514103
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to fix Datetime in MySQL? 4 51
Help with PHP 13 27
Force PDF to open inline as opposed to dowload 16 23
Javascript: Setting a dropdown based on another dropdown selection 5 20
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
The viewer will learn how to count occurrences of each item in an array.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question