How many queries on page load??

[karimali831]

Hi!

 

I am looking for a script that counts how many queries is executed on page load?

I hadn't realised one of my scripts was doing hundreds of queries simultaneously

and never would of thought this would be the cause of high load.

 

As I'm developing stuff in PHP, I want to ensure minimum queries are executed

especially because I am doing this in a procedural manner and not OOP.

 

Thanks in advance.

[BlueSkyIS]

i think your script is a script that can count the number of queries executed. start a counter at 0 and add 1 each time a query is executed. at the end, output counter.

[PFMaBiSmAd]

procedural manner and not OOP

 

^^^ That has nothing directly to do with how many queries you have written your code to execute on a page.

 

I'm going to guess that you have written your code to perform query(ies) inside of loop(s).

 

Counting your queries would involve using some sort of database abstraction layer, which could be as simply as making a function/class that executes the supplied query that you use to replace your existing query calls with. The code in the replacement query function/class would keep a count of the number of queries and possibly keep other information such as type of query and line number where the query was called from.

[karimali831]

I did some research and there is nothing under my search, I thought this sort of thing a lot of people would want to get their hands on. I am using many while loops from different files with many include() 's and calling many functions from different files that also has while loops inside.

[dcro2]

There's no function for getting the number of queries executed in one session, so your only solution is to replace all mysql_query calls with a custom function that calls it and increases a counter. Or if you're using a custom mysql class that handles all your MySQL, you can add it to that. Sorry for repeating what PFMaBiSmAd basically said, but that is the answer.

[PFMaBiSmAd]

A) You haven't supplied us with any specific information about even which database you are using, so a little hard to help,

 

B) If all you are using are mysql_query() statements, the following should get you started. It works for the few test cases I tried.

 

You should do this on an off-line back-up copy of your code and since you are likely going to be modifying and testing the code to eliminate the excess queries, you would be doing this on a development system anyway -

 

Add (include) this somewhere near the start of the main page you want to check -

 

class db_count{
public static $count = 0;
public static $stat = array();

public static function query($query,$con=null){

	self::$count++; // count this query

	// produce backtrace information
	$bt = debug_backtrace();
	$function = array();
	$file = array();
	$line = array();
	foreach($bt as $key => $arr){
		if($key >= 1){
			$function[] = $arr['function'];
			$file[] = basename($arr['file']);
			$line[] = $arr['line'];
		}
	}
	$function = implode('<--',$function);
	$file = implode('<--',$file);
	$line = implode('<--',$line);
	self::$stat[] = array('query'=>$query,'func'=>$function,'line'=>$line,'file'=>$file);

	// execute the query
	if(is_null($con)){
		$result = mysql_query($query);
	} else {
		$result = mysql_query($query,$con);		
	}
		return $result;
}
}

// functions are global so you can search and replace mysql_query( with my_query(
function my_query($query,$con=null){
return db_count::query($query,$con); // call the query method of the class
}

 

Search and replace all the occurrences of mysql_query( with my_query(

 

Add (include) the following near the end of the main page you want to check -

 

// display count and query information -
echo db_count::$count . " query(ies) on this page!<br />";
echo '<pre>',print_r(db_count::$stat,true),'</pre>';

 

When you are done, remove the two pieces of code and search and replace all occurrences of  my_query( with mysql_query(

[karimali831]

Thanks for your replies.

 

Well I am using a custom function to call mysql_query() which I recall safe_query()

 

// -- MYSQL QUERY FUNCTION -- //
$_mysql_querys = array();
function safe_query($query="") {
global $_mysql_querys;
if(stristr(str_replace(' ', '', $query), "unionselect")===FALSE AND stristr(str_replace(' ', '', $query), "union(select")===FALSE){
	$_mysql_querys[] = $query;
	if(empty($query)) return false;
	if(DEBUG == "OFF") $result = mysql_query($query) or die('Query failed!');
	else {
		$result = mysql_query($query) or die('Query failed: '
		.'<li>errorno='.mysql_errno()
		.'<li>error='.mysql_error()
		.'<li>query='.$query);
	}
	return $result;
}
else die();
}

 

and for every query I use safe_query()

 

does this help ?