Hello,

The setting to php.ini should be enough, that's odd.

As for your changes to config.inc.php and config.default.php-- neither of those are FlightPath files, so I'm afraid I cannot comment on them.

Maybe you also need to check your apache httpd.conf config to make sure there isn't also a maximum execution time there.

What exactly is the nightly job you are running, that it is dying on?

Richard

Well, the routines which talk to Banner could easily exceed 5 minutes, so that doesn't surprise me too much.

I am still not sure about the confic.inc files-- like I said, FlightPath doesn't use any such files. You can edit your /custom/settings.php file though-- maybe at the top of that, put an ini_set command like so:

ini_set('max_execution_time', 0);

That should mean to let the script run forever-- which you might not actually want to do globally, but at least that would let you see if that helps.

You might also try:
set_time_limit(0);

Which should have the same effect.

Included in the banner_integration module is a file called "sample_routine.php.txt". You can see in that file, I call set_time_limit(9999) which should let it run for over 2 hours.

Give that a try, at the start of /custom/settings.php, and then re-run your routine and see if that helps at all.

Good luck!
Richard

Yikes! I checked, and at ULM, that routine takes 18 minutes to run, and we end up with around 19,000 records in the advisor_student table in the FlightPath database (that's the table which is being populated).

How many records did you end up with after 9 hours? Also, how many students are you working with?

Even if it's 100,000 students, I still don't think it should take that long. The slowdown is either on the Banner side, or the MySQL side.

See what type of table the advisor_student table is in MySQL-- change it to MyISAM if it isn't already. If it's InnoDB, those can often be slower to write to than MyISAM.

I started looking at the routine, too, to see if there was anywhere things could be sped up. I change the banner_integration.module file, to use a simple caching scheme for lookup up CWIDs from Banner-- that should save a bit of time for you, and it's probably good practice anyway. If you want to try the new code, edit your banner_integration.module file. Around line 332 or so, is the function "banner_integration_get_cwid_for_pidm($pidm)" Erase the entire function, and replace it with this code:

/**
 * Return the student's CWID, based on their Banner pidm.
 *
 * @see banner_integration_get_pidm_for_cwid()
 */
function banner_integration_get_cwid_for_pidm($pidm) {

  // Return from cache because that will be faster.
  if (isset($GLOBALS["bi_cwid_for_pidm"][$pidm])) {
    return $GLOBALS["bi_cwid_for_pidm"][$pidm];
  }
  
  // Connect to Banner's oracle db
  banner_integration_connect_if_not_connected();

  $rtn = "";
  
  $res = oracleapi_query("SELECT spriden_id
                          FROM spriden
                          WHERE spriden_pidm = '?'
                          AND spriden_change_ind IS NULL ", $pidm);
  $cur = oracleapi_fetch_array($res);
  
  // Add to our cache for later.
  $GLOBALS["bi_cwid_for_pidm"][$pidm] = $cur["SPRIDEN_ID"];
  
  return $cur["SPRIDEN_ID"];    
  
}

I hope that helps! If you run into trouble replacing this function, let me know, and I will just upload a new version of the banner_integration module itself and you can use that instead.

Wow, you've got a lot of data!

I don't think you need to delete the tables entirely, but I would definitely start with switching the engine for advisor_student to MyISAM, and applying the code change I showed you, and see if that helps speed things up. You might want to empty the table first before you change the engine to MyISAM, or it may take a long time.

No, that global variable is one I made up just for that function. It's a very simple caching scheme. It gets set further down in the same funciton, where it says:
// Add to our cache for later.
$GLOBALS["bi_cwid_for_pidm"][$pidm] = $cur["SPRIDEN_ID"];

(spriden_id in Banner is the same thing as CWID in FlightPath).

It keeps it from having to re-query Banner if the data has already been found once during this routine run.

This is probably the biggest source of your slow-down. The function goes out and queries banner for a CWID for every record in advisor_student. In your case, that's 60K+ extra queries! I know this is terribly inefficient, but the only other thing I can think of would be for you to export everyone's pidm & spriden_id ahead of time to a local mysql table, then modify the function to read from that instead.

You could also decide to just use banner's pidm for FlightPath's CWID. As long as its a unique number per student & faculty, FlightPath will be happy to use it. And at least on our campus, the pidm in Banner fits that bill. But, I would do this only as a last resort, if you can't get the routine to speed up any other way. I say this because there may be other functions in the banner_integration module which assumes you are using the spriden_id for the CWID, and they'd need to be changed there too.

In the mean time, try this:

Implement the code change I mentioned in the previous message, and change your advisor_student table to use MyISAM for its engine. Then, re-run just that routine, and see what kind of performance difference it makes.

Good luck!

Richard

PS: Just in case you aren't that familiar with the differences b/t MyISAM and InnoDB engines, InnoDB is thought to be "safer" if you have a lot of users all writing to the same tables at the same time, because it locks the row before it writes. It can also do "transactions" where it does several queries at once, and can even roll-back if there was an error. This has the extra overhead of being slower, however, for inserts. MyISAM doesn't do this.

So, for average tables with lots of reads & writes throughout the day, InnoDB is probably still fine to use. But, for a table like these import tables (like advisor_student or students, etc), where you are only writing to it once a night in a routine, and thousands of records at a time, you don't really need InnoDB, and I'd go with the faster choice, MyISAM. At no other point are those tables being written to anyway.

Richard