4 ms·
The last optimization is semi-wrong. From my experience it's faster to make SQL queries in a loop then it is to make one huge SQL query that gets all the data,
by ars 15y ago
The last optimization is semi-wrong.
From my experience it's faster to make SQL queries in a loop then it is to make one huge SQL query that gets all the data, but requires post-processing in PHP.
Let me clarify:
If you are using all the data from every column and every row, then certainly - do one big query.
But a lot of the time you do code that gets a parent, then all the children.
You only output the parent once. So if you try to write a query that will return all the children at one, you also by necessity are returning the parent data multiple times - yet you only output the parent data once.
To do this typically you store a variable with the previous_parent_id, then check if the new row matches it in your client loop.
Don't do this. It's slower.
Get just the parent data and loop on it, then get the child data in individual queries.
The reason it's faster is database indexes. When you get the parent data you want a sort order - hopefully that column is indexed and the database can return it directly without sorting.
Same for the child data, you want it sorted, you have an index that covers the parent_id, and your sort column and the database can directly return the data to you.
But, if you try to join the parent and child table, only the parent data is pre-sorted. The child data will need to be sorted after the join - often on disk. This is terrible for performance. (The child index is used for the join, but not the sort.)
Additionally you are often transferring lots of data, because you are repeating the parent columns over and over uselessly. That's not free. Even if it's a local database the database server still needs to buffer all that data and so does the client.
Caveat: This is my experience with MySQL, it's possible other databases are able to use indexes to sort both the parent and child records, even through a join.
- aidos 15y agoIn this case you're generally better off querying once to get the parent data, collecting all the child keys from it and queuing a second time to collect ALL the children. The technique you've described is the reason early ORMs had a bad reputation wrt performance. I'd have seriously strong words with any of my developers I found querying inside a loop. Checkout the N+1 problem.
- ars 15y agoUnless you have 3 levels of tables to work through. Yes, you can get all the data from the third level in one query - but then you have to find the keys you want from it in your client, and without an index. (Alternately you may be building a hash-index on the fly each time you retrieve the data, which isn't optimal either. For example if you store the key in a PHP array.) Obviously you need to be smart about what you are doing. But simply always getting the data in bulk is not automatically correct.
- bad_user 15y agoIt really depends on the type of query you're making. If you're talking about a tree, here's how to get a whole sub-tree (as in all descendants of a parent), without fetching parents multiple times, without multiple queries, sorted in a previously defined order and also indexed and really fast: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://dev.mysql.com/tech-resources/articles/hierarchical-da... When you get the parent data you want a sort order Not necessarily. People use a sort order mostly to limit the number of rows returned (say you want only the first 50 items with the lowest prices). But optimizing a SELECT using ORDER BY is really hard as there are other restrictions you need to be aware about (like if you're using an index on multiple columns, you can't have a range condition on the first column and sort on the second, at least in MySql). That's why, if performance is an issue, there are ways to workaround the need to sort -- for example you can keep extra data, like page=1 if position is between 0 and 50, page=2 if position is between 50 and 100, and so on, such that LIMITing the query to the first 50 items is WHERE page=1 (basically storage-efficient precached queries - if the conditions are stable, you can do it). And in cases where you can't fetch the data efficiently in a single query, you're probably doing it wrong (like you chose the wrong data representation - for example the relational model is really awful for describing anything related to graphs). Of course, I'm not talking about cases when you're fetching unrelated data or cases where performance doesn't matter or cases when you've got BLOBs in your parent :)
- ars 15y agoYou didn't really understand what I meant, which I guess is my fault. When I say parent I don't mean in the same table, I mean an unrelated parent (1 to many join). And I use sort all the time without limit, I think it's important for data to always be displayed in a consistent order. > And in cases where you can't fetch the data efficiently in a single query, you're probably doing it wrong Example: I have a list of buildings, then I have a list of room, then a list of contents. I want to create a giant page on the website displaying this data. One table for each building, one row for each room, one cell for each content. You can not get all this data efficiently in a single query. You can get the data by joining all three tables, but you will be repeating the data about the building over and over for each content.
- Joeri 15y agoThat last example is poorly chosen on other levels as well: - Because the values are concatenated into the query it makes the query harder to cache for the database. For performance reasons queries should always be prepared / parametrized. On Oracle this makes such a big difference that at a big customer of my employer a single query by a different app in its own schema (ran lots of times) was the bottleneck causing five-fold decreases in responsiveness in our app on our schema. - There's no escaping at all, this is like an open door for hackers. Again, with parametrized queries there would have been less risk (you can still get SQL injection in triggers, but the risk is definitely lower).
- justincormack 15y agoOutside Oracle prepared queries make much less difference. They can improve performance is Postrgres used sparingly. Obviously escaping is rather important...
- vog 15y agoYou can also let the database aggregate that tree for you. For instance, PostgreSQL allows for ARRAY/RECORD data types that can be used for aggregating. In addition, XML datatypes can be handy here. Those are generic, so you don't need to defined your RECORD types. However, if you use lots of XML elements instead of XML attributes, the communication overhead due to the big XML serialization might defeat performance. This is why I prefer to aggregate JSON structures instead. Unfortunately, PostgreSQL doesn't yet have good JSON support, neither natively nor via contrib. So this requires a custom JSON implementation. If you use a database without advanced data structures (such as MySQL) you can also try to aggregate your data by string concatenation. Of course, you need a non-occuring separater character, or better an escaping function here. This might still be fast, but will make your SQL query hard to write and hard to maintain.