Performance of returning entire tables containing blog text as opposed to selecting specific columns

Question

I think this is a pretty common scenario: I have a webpage that's returning links and excerpts to the 10 most recent blog entries.

If I just queried the entire table, I could use my ORM mapped object, but I'd be downloading all the blog text.

If I restricted the query to just the columns that I need, I'd be defining another class that'll hold just those required fields.

How bad is the performance hit if I were to query entire rows? Is it worth selecting just what I need?

Answer 1

It depends, but it will never be as efficient as returning only the columns you need (obviously). If there are few rows and the row sizes are small, then network bandwidth won't be affected too badly.

But, returning only the columns you need increases the chance that there is a covering index that can be used to satisfy the query, and that can make a big difference in the time a query takes to execute.

Answer 2

The answer is "it depends".

There are two things that affect performance as far as column selection:

1. Are there covering indexes? E.g. if there is an index containing ALL of the columns in the smaller query, then a smaller column set would be extremely benefifical performance wise, since the index would be read without reading any rows themselves.

2. Size of columns. Basically, count how big the size of the entire row is, vs. size of only the columns in smaller query.

   • If the ratio is significant (e.g. full row is 3x bigger), then you might have significant savings in both IO (for retrieval) and network (for transmission) cost.

   • If the ratio is more like 10% benefit, it might not be worth it as far as DB performance gain.

Answer 3

,Since you specify that it's for 10 records, the answer changes from "It Depends" to "Don't spend even a second worrying about this".

Unless your server is in another country on a dialup connection, wire time for 10 records will be zero, regardless of how many bytes you shave off each row. It's simply not something worth optimizing for.

So for this case, you get to set your ORM free to grab you those records in the least efficient manner it can come up with. If your situation changes, and you suddenly need more than, say, 1000 records at once, then you can come back and we'll make fun of you for not specifying columns, but for now you get a free pass.

For extra credit, once you start issuing this homepage query more than 10x per second, you can add caching on the server to avoid repeatedly hitting the database. That'll get you a lot more bang for your buck than optimizing the query.