Menu
I'd like to know, which option is the most expensive in terms of bandwith and overall efficiency.
Let's say I have a class Client in my application and a table client in my database.
Is it better to have one static function Client.getById that retrieves the whole client record or many (Client.getNameById, Client.getMobileNumberById, etc.) that retrieve individual fields?
If a single record has a lot of fields and I end up using one or two in the current script, is it still better to retrieve everything and decide inside the application what to do with all the data?
I'm using PHP and MySQL by the way.
605
However, in the context of the question, a single large query will be faster that, let's say -in the worse possible scenario- a SELECT inside a programming loop (no matter the RDBMS used).
For subqueries and joins, the data needs to be combined. Small amounts can easily be combined in memory, but if the data gets bigger, then it might not fit, causing the need to swap temporary data to disk, degrading performance. So, there is no general rule to say which one is faster.
Preserving performance through UNION The UNION operation allows us to merge the results of two queries. Since we know that query #1 and query #3 are each significantly faster than query #2, we would expect that the results of the UNION operation will be fast as well.
The single query is one SELECT statement, whereas the compound query includes two or more SELECT statements. Compound queries are formed by using some type of operator to join the two queries. The UNION operator in the following examples is used to join two queries.
Is it better to have one static function Client.getById that retrieves the whole client record or many (Client.getNameById, Client.getMobileNumberById, etc.) that retrieve individual fields?
Yes, it is.
Network latency and lag as well as the overheads of establishing a connection mean that making as small a number of database calls as possible is the best way to keep the database from saturation.
If the size of the data is really so much that you see an effect, you can consider retrieval of the specific fields you need in one single query (tailor the queries to the data).
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
