I'm working with a bunch of manufacturing processes and trying to create a basic auto-scheduler. This is focusing on gathering the requirements from the DB2 server our system is on.
On a previous incarnation I queried the orders for each part by itself based on days, then transformed those days into mondays to group the orders by weeks and then propagated the requirements down through the components and finally storing all of that in specific excel files for the given resource.
In the newest incarnation I've built a database with the bill of materials information, and I query for all of those orders at once to build raw data files for the different kinds of processes and in order to get the schedules for each component I'm parsing those raw files and building specific excel files for the schedules.
My question then is: Is it more efficient to limit queries or limit excel look ups? I've done some looking at other PHPExcel efficiency questions and found a few changes to make to improve that, and have also done the same with MySQL queries, but in general what is a more efficient way to do what I'm looking at? (As an additional note, the server I'm running my MySQL database on has enough RAM to store the entire database there, which I know increases the speed, but I'm not sure if that should be a determining factor as that fact might eventually change)
MySQL is really good at optimizing queries. What you need is the right indexes. Making a SQL query on an index will definitely be faster than parsing the data using PHPExcel (or any other library).
This is especially true for PHPExcel if your dataset is large: PHPExcel requires a lot of memory so you are likely to encounter an "Out of Memory" issue. You can workaround this by caching data but this will badly affect the overall performance.
So my advice is to make sure your tables are correctly indexed, get the data you need from MySQL and avoid filtering this data in your application. This pattern works for the vast majority of use cases and scales well.