Efficiancy Help - This is way too slow (help!)

big_nerd

I need a little assistance with Making this code a little more efficiant, if anyone can help it would be greatly apreciated.

/*
Top 10 Models

This Queries a Database, there is approx 2000 Different Model Numbers and around 1200 Unique Models
The intention is to get the top 10 models in an array.

*/
$query = "select model, udate from techcc where udate between '$modelfrom' and '$modelto' ";
$result = mssql_query($query);
$array_size = mssql_num_rows($result);

$t = 0;
while ($row = mssql_fetch_object($result)) { // Initialize the array
	$t++;
	$ran[$t] = $row->model;
	//echo $row->model;
}


// The bubble sort method.  If you don't know how it works it's very
// simple, values are switched one at a time for each element. 
// Bubble Sort Method Taken from Internet Resource, at this time I can not remember where, notes all over desk :) 

for($x = 0; $x < $array_size; $x++) {
  for($y = 0; $y < $array_size; $y++) {
    if($ran[$x] < $ran[$y]) {
      $hold = $ran[$x];
      $ran[$x] = $ran[$y];
      $ran[$y] = $hold;
    }
  }
}

$difmodel = 0;
$counter = 1;
$tcount = 0;
$modname = array();
$modnum = array();

for($x = 0; $x <= $array_size; $x++) {
	//echo $ran[$x] ."<br>";
	$test = $ran[$x];		
	$previ = ($x - 1);
	$cur = $ran[$x];
	$pas = $ran[$previ];
	$pas = ereg_replace("/","",$pas);
	if ($cur != $pas) { // If The Model number is different from the previous, must be a new one
		$difmodel++;
		if (!$pas == "" && $pas != "NA") {		
		$modname[$difmodel] = $pas;
		$modnum[$difmodel] = $counter;
		$tcount++;
		}
		$counter = 1;
	} else {			// If the model number is the same, increase the count
		$counter++;

}	
}


$complete = array();

for ($x = 0; $x <= $array_size; $x++) {
	$complete[$x] = $ran[$x];
}
echo "<hr><b>Different Models:</b> $difmodel - <b>Total:</b> $array_size<br>";

for($x = 0; $x < $array_size; $x++) {
  for($y = 0; $y < $array_size; $y++) {
	if($modnum[$x] > $modnum[$y]) {
      $hold = $modnum[$x];
	  $modnum[$x] = $modnum[$y];
  	  $modnum[$y] = $hold;
	  $hold = $modname[$x];
	  $modname[$x] = $modname[$y];
  	  $modname[$y] = $hold;		  
    }
  }
}
echo "<hr><b>Top 10 Models:</b><br><br>\n";
for($x = 1; $x <= 10; $x++) { // Display all the models and Their counts
	echo $modname[$x]." - ".$modnum[$x]."<br>\n";
}	
echo "<input type=\"hidden\" name=\"model\" value=\"1\">\n";	
echo "<input type=\"hidden\" name=\"modelfrom\" value=\"$modelfrom\">\n";	
echo "<input type=\"hidden\" name=\"modelto\" value=\"$modelto\">\n";

If anyone can help take a little less time to run, that would be amazing.

Regards,

Michael

Piranha

Where in your code do you have trouble with the speed? Use microtime to find out.

More generally I can say that bubble sort is really slow and you use it two times. It is better to sort the result in the database and get the result already sorted. Also think about indexing update in the database and add a limit in the query.

When I had already written the things above I noticed that you do bubblesort 2 times on all the results, and then only show 10 of them. That is probably why it is so slow. Look at limit in the MySQL manual and you will gain much.

laserlight

I agree, it makes sense to sort using your query. If you do insist on sorting with PHP, PHP itself provides functions such as [man]sort/man, which is likely to be faster than an unoptimised version of bubble sort.

big_nerd

Thanks! I will look into the limit option.

This is being used with other tickets, so sorting in the database (permanently) is not an option (it is to capture calls taken in by a small sector of a call center).

If there is a function to return the count of each occurance I could use an "ORDER BY xx ASC" in the query string.

I have looked at array_count_values() however I am unsure on how to parse the output.

Any ideas on how I would avoid using bubble sorts without saving the information to the database? This is used by multipul users.

I have looked into asort() etc as well, but I suppose I lack the skill to appropriately use this function for my current needs.

Regards,

Michael (big nerd)

laserlight

This is being used with other tickets, so sorting in the database (permanently) is not an option (it is to capture calls taken in by a small sector of a call center).

Sorting in a query does not change the ordering of the data as it is stored in the database.

big_nerd

Laser, sorry I misunderstood at first, I was under the impression of changing the sort in the database, I have gotten myself a coffee in order to disallow this from happening again.

Also, I have changed the query to include: "order by model ASC" and scrapped the first useless bubble sort all together.

It is still quite slow.

The function here, is obviously what is doing it, is there a way then, to check the number of occurences of each string, and return an array of the top 10 or so results?

	for($x = 0; $x < $array_size; $x++) {
	  for($y = 0; $y < $array_size; $y++) {
    	if($modnum[$x] > $modnum[$y]) {
	      $hold = $modnum[$x];
    	  $modnum[$x] = $modnum[$y];
      	  $modnum[$y] = $hold;
		  $hold = $modname[$x];
		  $modname[$x] = $modname[$y];
      	  $modname[$y] = $hold;		  

	    }
	  }
	}
	echo "<hr><b>Top 10 Models:</b><br><br>\n";
	for($x = 1; $x <= 10; $x++) { // Display all the models and Their counts
		echo $modname[$x]." - ".$modnum[$x]."<br>\n";
	}

Regards,

Michael (big nerd)

Piranha

First I have to say that it is impossible to change the sorting in the database. Why? Because a RDBMS stores everything totally unsorted, in other words all sorting that takes place is done with values in the columns or any other means in the query.

Second look at the COUNT function in SQL. If you want to sort something it works something like this:

SELECT name, COUNT(id) as cid
FROM table
ORDER BY cid
GROUP BY name
LIMIT 0, 10

SELECT name
FROM table
ORDER BY count(id)
GROUP BY name
LIMIT 0, 10

Notice that I have never managed to learn if ORDER BY or GROUP BY should be first. Test it and you will see.

big_nerd

Is this specific to MySQL? This is running on a MS SQL 2K Server.

I am off for the next few days so I will not be able to test it too quickly (i dont have sufficant data here at home)

Just to verify, as I was unable to get it working at first.

Regards,

Michael (big nerd)

Piranha

It should work in all SQL databases. It might be that it si not possible to order by cid, that you have to order by count(id) instead. There may be some small changes when it comes to exactly how you write it, but as far as I'm aware of this is basic enough to not have those changes.

Weedpacket

MS SQL does not have a LIMIT clause, bizarrely enough; you need to select the TOP 10 (or whatever). Consult thy documentation for details.

Incidentally, bubble sort is probably the second worst algorithm for sorting a list. The only one I can think of that's worse is to repeatedly randomise the list and look to see if the result happens to be in order.

Piranha

Wow, I knew Microsoft was special, but I really thought that they had the LIMIT in their queries. Anyway I found an article that states how you can handle it.