Welcome Guest, Not a member yet? Register   Sign In
best way to create nested arrays from mySQL
#2

This all really depends on where you experience slow-downs in the process. For example, you could find that the query to retrieve all of the data at once is significantly faster or slower than retrieving it as multiple queries. In fact, this particular issue is responsible for a number of different design patterns related to data access.

In most cases, no matter which path you choose, the database access itself is more likely to be the performance bottleneck than the PHP code required to transform the returned data into a JSON-encoded object for your script. The several hundred database calls is the slowdown, not the fact that PHP is responsible for the record associations, because adding several hundred entries to an object or array in PHP does not require a significant amount of time.

There are pre-existing libraries (like DataMapper) to handle most of this in a generalized fashion, which will certainly generate more database calls than is strictly necessary, but will work well for most cases. If this is the only time you're going to run into a problem like this in your application, whether it is worth your time to look into them is up to you. If you're going to run into it a lot, it will probably be worthwhile. In most cases, something like DataMapper will be good enough for performance in a situation like this.

Occasionally, though, the database access is excessive and you end up needing to optimize the data access by writing something specific to your needs, in which case you're almost back to where you started. Generally, I've found that if you're really running into a situation where you might be making a significant number of queries to the database to build your dataset, it's faster to make the joins in SQL and reduce the dataset in PHP. The code required is usually not as unwieldy as it might seem at first glance, though you may need to make use of recursion and leave copious notes behind via code comments to ensure you can debug it later if an issue (or change) comes up.

Another thought is that a blended approach may serve you better. For example, you could retrieve the Customers in one query and combine the Orders and OrderLines in a second query, grouped by CustomerID, or retrieve the Customers and Orders in one query and the OrderLines (grouped by OrderID) in a second (or even retrieve the data from each table as a separate query, but limiting it to three queries, for example "select * from Orders where CustomerID in $arrayOfCustomerIDs"). This would reduce the complexity of reducing the dataset, while preventing the potentially hundreds of queries on the database.

Ultimately, the idea of storing JSON in the database for completed orders is not a bad one, either, as long as the table of JSON data is used as a performance cache rather than as the data store. You could insert the data when the order is finalized and set foreign key fields on the table which would allow updates if something changes in a completed order (for some reason). I would recommend keeping the completed orders in the original tables, though, simply because you may find some day that you want (or need) to change the format of your JSON objects or the JSON proves to be an additional headache for a simple database operation.
Reply


Messages In This Thread
RE: best way to create nested arrays from mySQL - by mwhitney - 03-05-2015, 08:29 AM



Theme © iAndrew 2016 - Forum software by © MyBB