Solved

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

Posted on 2011-09-02
24
290 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 108

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
 
LVL 108

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 108

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 108

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 108

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 108

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 108

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 108

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now