Help streamlining script...

shawjames

Hello,
Okay, I have a script that my warehouse runs every day to see what orders are ready to ship. There are 3 tables that it checks:

It checks the order table to see which orders are waiting to ship.
It checks the products table to see what products are in stock and available to ship.
It checks the items table for each open order to see what products are needed for the order.

The catch is that it has to track how many orders have the same item, so that it wont say all orders with one item can ship, even when only one is in stock... So basically when it gets to the first order of an item that has stock of only one, it will say it can ship... but not for the next one. That might be confusing...

I have a script and been running it for about a year. Sometimes it will time out, but it always takes about 15-20 min to run. I use the set_time_limit() function to prevent server timeout, but sometimes it still "hangs" while running.

The current script does this:
1. Loads the entire warehouse inventory from the products table, into a 2 column array, one for the part number and the other for the inventory count. (~300 orders)
2. Loads all of the pending orders into an array (~1000 items)
3. Loops the orders array and does this:
-Checks the items table for all items on the order (1-50 items for each order usually)
-For each item, it loops through the inventory array. If found and the quantity is enough to cover the amount on order, it creates a temp inventory array and adjusts the quantity. If all the items in the order are found then it saves the temp array over the main. If one item is not found, it abandons the temp array and moves on to the next order.

Then it takes the list of orders that can ship and passes it on to the next script which generates a packling list that can be printed up.

So what I am looking for help on, is a better way to do this. I can take care of the actual coding part, but I need help with a faster method. I do not know what really makes the script run slow.. besides for the large number of operations...

Any ideas?

Thanks!!

shawjames

I think I have it figured out... trying to place breaks in strategic places to get out of the nested loops when they are no longer valid...

Still runs slow.

rachel2004

Originally posted by shawjames
The current script does this:
1. Loads the entire warehouse inventory from the products table, into a 2 column array, one for the part number and the other for the inventory count. (~300 orders)
2. Loads all of the pending orders into an array (~1000 items)
3. Loops the orders array and does this:
-Checks the items table for all items on the order (1-50 items for each order usually)
-For each item, it loops through the inventory array. If found and the quantity is enough to cover the amount on order, it creates a temp inventory array and adjusts the quantity.

The numbers 300, 1000, or even more doesn't seem "that bad", but it also depends on what each of those rows is retrieving for you. If one row equals 512K, you can do the math and realize that 300 times that amount or 1000 times that amount is a lot of data that is being moved from the database server to the server running your PHP code. Don't be greedy with your query. If you all you need to know is the "count" of an item, then ask for the count; don't ask for SKU number, product description, price, and so forth. You get my point.

Second, consider adding indexes to your most heavily queried columns. You should see an immediate increase in speed afterwards.

Aside from that, we'd have to see table structures (db design) and how your queries look to get any more of a performance increase. But try my first two suggestions first.

Hope this helped.