[RESOLVED] Faster Method Than 30 Queries

[bronzino]

I am building a pedigree chart from a table full of horse data.The pedigree looks like this when it is loaded with data. The data in the table looks like this:

PedKey [int] -- HorseName [text] -- Sire [int] -- Dam [int]

Where the Sire = the PedKey of another horse and the Dam = the PedKey of another horse or row in the table. This is a six generation pedigree, so I made 30+ queries. The first query looks for the horse associated with the pedigree, and the rest look for the remaining relatives, as below.

The page is loading slowly. Since my other pages load lickety-split, I'm assuming the problem is the number of queries. Would a Union query be more efficient? Speedier?

// Foal
$query_pedigree=sprintf("SELECT * FROM Pedigrees where PedKey=%s and Publish!='No'",GetSQLValueString($ID,"int"));
$pedigree = mysql_query($query_pedigree, $CR) or die(mysql_error());
$row_pedigree = mysql_fetch_assoc($pedigree);

// Sire
$var_A02=$row_pedigree['Sire'];
$query_A02=sprintf("SELECT * FROM Pedigrees Where Publish!='No' and PedKey=%s", GetSQLValueString($var_A02, "int"));
$A02 = mysql_query($query_A02, $CR) or die(mysql_error());
$row_A02= mysql_fetch_assoc($A02);

//Grand-Sire
$var_A03=$row_A02['Sire'];
$query_A03=sprintf("SELECT * FROM Pedigrees Where Publish!='No' and PedKey=%s", GetSQLValueString($var_A03, "int"));
$A03 = mysql_query($query_A03, $CR) or die(mysql_error());
$row_A03= mysql_fetch_assoc($A03);

[dalecosp]

Can you adjust the queries to use EQUAL instead of NOT EQUAL? What does that do to performance?

[bronzino]

dalecosp;11003582 wrote:

Can you adjust the queries to use EQUAL instead of NOT EQUAL? What does that do to performance?

It's improved. By about 25%-30%, I'd say.

Thanks for your response - just learned something new. 🙂

Any other suggestions for getting it to go more quickly?

[dalecosp]

Well, are you really looking for all fields? If not, I might try asking only for the fields I need.

SELECT field1, field2, field8, from table where foo=bar

instead of

SELECT * from table where foo=bar

But I don't expect a large performance increase from that. You might try and find some reading material (web or otherwise) about SQL optimization. Of course, sometimes you don't have the option of re-designing the database ....

[dalecosp]

I guess my other question would be something like, what does the GetSQLValueString function look like?

[NogDog]

It might be more efficient to do some joins, something like:

SELECT
  p1.col_of_interest_1 AS this_1,
  p1.col_of_interest_2 AS this_2,
  p2.col_of_interest AS sire_1,
  p3.col_of_interest AS grand_sire_1
FROM Pedigrees AS p1
LEFT JOIN Pedigrees AS p2 ON p1.Sire = p2.PedKey
LEFT JOIN Pedigrees AS p3 ON p2.Sire = p3.PedKey
WHERE
  p1.PedKey = %s     AND
  p1.Publish != 'No' AND
  p2.Publish != 'No' AND
  p3.Publish != 'No'

And of course make sure you have indexes on each column that appears in the JOIN and WHERE clauses.

PS: I would've posted before Brad, except I actually took the time to mock up a query. :p

[Weedpacket]

But this is supposed to go back six generations, not just two; this is a whole tree. I'd suggest a recursive CTE but I don't know what MySQL is like with those.

[NogDog]

Maybe restructure the data via the nested set model?

[bronzino]

Hi Dale - the Get SQLValueString function is below. I think it's just Dreamweaver's way of forcing newbs like myself to use Escape Strings.

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

[bronzino]

Thanks to all of you for your responses.

Weedpacket is correct - it is a six generation pedigree. Which means that without joins, I have 62 (I underestimated in the first post) different queries, because a six generation pedigree produces that many ancestors.

I'm looking at that nested tree model. It looks pretty close to what I want; the only thing I'm not certain of is: In the sample, the result is this:

ELECTRONICS
--TELEVISIONS
-----Tubes
-----LCD
--PORTABLES
-----CD Players
-----Radios

In my result, I need something like this:

-----Radios 1
-----CD Players 1
--PORTABLES 1
-----LCD 1
-----Tubes 1
--TELEVISIONS 1
ELECTRONICS 2
--TELEVISIONS 2
-----Tubes 2
-----LCD 2
--PORTABLES 2
-----CD Players 2
-----Radios 2

If the Nested Model won't work, then it sounds like I should use a whole bunch of left joins, using NogDog's sample as a starting point.

I looked up "CTE Model" and found this Stack Overflow post on it. From what I'm getting, it would basically be a series of joins that produce a temporary table of some sort. I haven't done that before, but I can try.

[dalecosp]

bronzino;11003644 wrote:

Hi Dale - the Get SQLValueString function is below. I think it's just Dreamweaver's way of forcing newbs like myself to use Escape Strings.

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

I concur with your evaluation. If you know the exact environment your app will have, you could shave a very small amount of time off by omitting this, maybe. It'd be much better to look someplace else.

Weedpacket;11003599 wrote:

But this is supposed to go back six generations, not just two; this is a whole tree. I'd suggest a recursive CTE but I don't know what MySQL is like with those.

Unsupported as of yet, from what I can tell

bronzino;11003645 wrote:

Thanks to all of you for your responses.

Weedpacket is correct - it is a six generation pedigree. Which means that without joins, I have 62 (I underestimated in the first post) different queries, because a six generation pedigree produces that many ancestors.

I'm looking at that nested tree model. It looks pretty close to what I want; the only thing I'm not certain of is: In the sample, the result is this:

ELECTRONICS
--TELEVISIONS
-----Tubes
-----LCD
--PORTABLES
-----CD Players
-----Radios

In my result, I need something like this:

-----Radios 1
-----CD Players 1
--PORTABLES 1
-----LCD 1
-----Tubes 1
--TELEVISIONS 1
ELECTRONICS 2
--TELEVISIONS 2
-----Tubes 2
-----LCD 2
--PORTABLES 2
-----CD Players 2
-----Radios 2

If the Nested Model won't work, then it sounds like I should use a whole bunch of left joins, using NogDog's sample as a starting point.

I looked up "CTE Model" and found this Stack Overflow post on it. From what I'm getting, it would basically be a series of joins that produce a temporary table of some sort. I haven't done that before, but I can try.

Do read the link NogDog posted ... good stuff. As for this, it almost looks like a formatting issue; keep in mind that you can still leverage PHP's power separately from MySQL's processing abilities.

For example, would it be possible to attack this in two sections ... the Sire's family tree, the Dam's family tree ... store each in arrays and print one backwards (like array_reverse() or something)?

[bronzino]

dalecosp;11003686 wrote:

Do read the link NogDog posted ... good stuff. As for this, it almost looks like a formatting issue; keep in mind that you can still leverage PHP's power separately from MySQL's processing abilities.

For example, would it be possible to attack this in two sections ... the Sire's family tree, the Dam's family tree ... store each in arrays and print one backwards (like array_reverse() or something)?

Yes it would be. Very possible!

I appreciate everyone's comments. I will try to attack this on the weekend and see what I get. I am going to mark this thread as resolved and then post again if I have any issues. And there are sure to be some, because I'm REALLY lousy with arrays. 😃

Thank you all again.