Probably the biggest thing affecting performance is your layout design. The PHP engine is very layout-centric. When the API requests records from the database, it will fetch all fields on the layout you specify. Hence, the biggest thing you can do to improve performance is to put only the fields on the layout that you actually need for the particular operation you're doing at the time.
For example, if you're fetching records for a list view with, say, an ID, a title, and a name, just create a layout that has those three fields on it and use that for the Find. Don't use a layout that has all the fields in the table on it, because the API will fetch all of them whether your page needs them or not.
This may mean that you have multiple layouts for the same table in your database. That's okay. The performance boost is worth it.
Other database or code design conventions that may affect performance include:
- In general, minimize the number of database calls you make. Use variables and arrays to store the data and then grab it from there rather than making trips back to the database.
- Unstored calculations. Avoid.
- Portals vs. multiple trips to other layouts with the related records. Some say doing finds on related records are faster in portals; others say you're better off making two trips to the database. Test to determine which way is better in your particular case. (This will vary based on the design of the database and the particular Find being performed.)
- Value lists - especially value lists based on database tables - can hurt. One trick is to store them in the $_SESSION superglobal and fetch them from there (only one trip to the database that way). Another trick is to do a Find and loop over the records to build the list rather than use the API value list functions. Yet another trick is to use a text file to store the value list; PHP will fetch this as an array (and, as a further bonus, you can give your customer access to it to edit - if you trust them not to screw it up).
As far as hardware goes, in my experience, I've not seen a big difference in a single server vs. a multiple server deployment. Others may have different experiences. However, there is one hardware-related item that I have seen that I would recommend looking at:
Network bandwidth / speed. Huge.
Thank you Mike. Great help. I will look at all the things you mention.