Multiple Large Queries Slows Everything Down

[ClayBitner]

Here's my predicament:

I have a program that will assign one item for multiple users on multiple dates. I need to also check to see if they have something assigned on that date. If so, I need to update it (unless it's already been journalled), if not, I need to insert it.

So far I have tried many different options.

1st Try: Loop Through Dates, Loop Through Users, run SQL that checks each user/date. (eg. select journaled from assign where uid = '234' and datetodo = '2006-01-01') Problem is that if I'm assigning to every person on every day that month, it will have to run 40*30 = 1200 select queries. Plus the insert or update query. Which means it will have to run 2400 queries. Not only does this take an insane amount of time, but it seems to quit after it's run about 400 queries. So only 6 full days get assigned (If I'm lucky).

2nd Try: Loop Through Dates, Loop Through Users and build a huge mother SQL query that will run once. (eg. select journaled from assign where (uid = '234' and datetodo = '2006-01-01') or (uid = '235' and datetodo = '2006-01-02')...etc) Then run a loop that goes through the results of the query and adds to a multidimentional array. $journaled[$date][$user] = t or f if journaled or not. Then will loop through the dates loop through the users once again, but this time check the array to see if journalled or not. If journaled, run the update query. If not, add to an insert query which ends up like this (insert into assign (uid, datetodo) values ('234', '2006-01-01'), ('235', '2006-01-01'), ...etc). This makes it so that there are a minimum of 2 queries, with a maximum of 1200 queries if everyone already has something assigned and you need to update each user/date. Problem is that the huge select statement takes ages to load. Then the huge insert statement takes ages to load too. It seems it's as slow if not slower than the 1st try. Only positive is that it doesn't time out the database as often. PHP sometimes times out, but i changed php.ini so that it didn't anymore. (But it's still taking like 5 mins to run the page at this point).

3rd Try: Loop Through Dates, Loop Through Users and build a single SQL query at every date loop. So it runs select journaled from assign where (uid = '234' and datetodo = '2006-01-01') or (uid = '235' and datetodo = '2006-01-01')...etc for each date instead of one massive one. Which means that now there's 30 select queries. After it runs the select query it loops through the result and adds to the multidimensional array $journaled[$date][$user] before it goes to the next date. Then it loops through dates and users again and adds to a semi-large insert query per date (same as select), so there would be up to 30 insert queries also. Again, update queries may still be up to 1200 because I'm not doing multiple updates in a single query. This one seems to be the fastest and most reliable so far, but I still sometimes get up to day 27 or 28 out of 30 and MySQL stops with an error:
"Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"
Then nothing else loads properly on the page and MySQL refuses to work until a refresh.


Is there a better method I could be using?
Something more efficient that will still keep the speed down?
By The way, I'm using PHP 4.3.10 and MySQL 4.025

Here's the current code I'm using if anyone wants to look through it:

[code]
$numassignwho = count($assignwho);
      $dbquerylarge = "";
      $initialquery = "";
      for ($i=0;$i<$numassignwhen;$i++) {
        $datetodo = $assignwhen[$i];
        $person_ids = array();
        for ($j=0;$j<$numassignwho;$j++) {
          $uid = $assignwho[$j];
          if (!$initialquery) {
              $initialquery = "select * from assigns where (uid='$uid' and datetodo='$datetodo' and type='$exercisetype')";
          } else {
              $initialquery .= " or (uid='$uid' and datetodo='$datetodo' and type='$exercisetype')";
          }
        }
          echo "<!-- $initialquery -->";
          //echo "$initialquery<br>";
          $dbresult = mysql_query($initialquery);
          $journalled = array();
          $dbnumrows = mysql_num_rows($dbresult);
          $totalprogress = ($totalqueries * 2) + $dbnumrows;
          while ($dbrow = mysql_fetch_object($dbresult)) {
            if (!is_array($journaled[$dbrow->uid])) {
                $journaled[$dbrow->uid] = array();
            }
            $journaled[$dbrow->uid][$dbrow->datetodo] = $dbrow->journaled;
          }
          $initialquery = "";
      }

      $dbquerylarge = "";
      for ($i=0;$i<$numassignwhen;$i++) {
        $datetodo = $assignwhen[$i];
        $person_ids = array();
        for ($j=0;$j<$numassignwho;$j++) {
          $uid = $assignwho[$j];
          if (!in_array($uid, $person_ids)) {
                array_push($person_ids, $uid);
              if ($journaled[$uid][$datetodo]) {
                if ($journaled[$uid][$datetodo] == "f") {
                    $dbquery = "update assigns set erid=$erid where uid=$uid and datetodo='$datetodo' and type='$type'\r\n";
                    $dbresult = mysql_query($dbquery);
                }
              } else {
                if ($dbquerylarge) {
                    $dbquerylarge .= ", ($erid,$uid,'$datetodo','$type')";
                } else {
                    $dbquerylarge = "insert into assigns (erid,uid,datetodo,type) values ($erid,$uid,'$datetodo','$type')";
                }
                
              }
          }
        }
        echo "<!-- $dbquerylarge -->";
        if ($dbquerylarge) {
            $dbresult = mysql_query($dbquerylarge);
            $queriesrun ++;
            if (!$dbresult) {
                $problems .= "<li>DB ERROR: error inserting exerciseroutines.<br>error = ".mysql_error($dbconn)."<br>query = $dbquerylarge\n";
            }
        }
        $dbquerylarge = "";
      }
      
    }
[/code]

[fenway]

That is quite a complex query -- why don't you use REPLACE instead of INSERT?

[ClayBitner]

[!--quoteo(post=347754:date=Feb 20 2006, 03:20 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 20 2006, 03:20 PM) [snapback]347754[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That is quite a complex query -- why don't you use REPLACE instead of INSERT?
[/quote]

I wish I could, but replace is not an option for me.
The unique ID is just an assign ID (eaid). So a replace would insert regardless of if there was already something assigned to that user on that date (and type). And there are already multiple assigns that had happened a long time ago that I don't want to have to find and delete in order to alter the table to add new unique characteristics.

[fenway]

I see -- depending on how your DB is set up, you could always determine that value ahead of time, and put in "back" in the REPLACE. But I see how this could get complicated. It's hard to get around the fact that you need to create so many records. What if you get all the people you have to update in one round, and all the people you need to add in another?