0

I have a database table with 20 columns but I only need to work with 8 of the columns.

Is it more efficient to return the whole table

_context.products.where(x => x.active);

Or is it better to do this

_context.products.where(x => x.active).select(x => new SubModels.ProductItem { id = x.id, name = x.name, category = x.category etc etc});

Thanks

Bad Dub
  • 1,503
  • 2
  • 22
  • 52
  • 9
    It is always more efficient to return only the data you need. – Magnus Nov 07 '16 at 15:13
  • Yeah thats what I thought I remembered reading a while back, just wanted to confirm it. Thanks! – Bad Dub Nov 07 '16 at 15:15
  • I'd be surprised if there was much in it though – Liam Nov 07 '16 at 15:15
  • The function as a whole is pretty demanding so I need to make every thing as efficient as possible. – Bad Dub Nov 07 '16 at 15:17
  • If `_context.products` is a collection of model objects that are already there, it would be faster to select existing objects than create new ones (though probably not faster enough to matter). But if you're getting data from a server via Entity Framework or LINQ to SQL, then you're creating new objects anyway, and you want to minimize bandwidth. It's the latter case, right? I think you're missing a tag. "LINQ" doesn't mean "query the server". – 15ee8f99-57ff-4f92-890c-b56153 Nov 07 '16 at 15:17
  • I think the latter way seems to be the best way for my needs as Im working with two contexts and if I dont cast as the model the code complains about using two different contexts. – Bad Dub Nov 07 '16 at 15:19

2 Answers2

2

You can pretty much refer to this question "Why is SELECT * considered harmful".

It seems rather relevant to your case and has a lot of points.

So it's always better from performance point of view of one query to select only what you need. But you should always consider some other things like likelihood of issuing additional queries to get the data you could have returned already.

Community
  • 1
  • 1
Ilya Chernomordik
  • 27,817
  • 27
  • 121
  • 207
0

The select is more efficient, because it queries just the data you need. on your first example, it will query all columns on the db.

Stormhashe
  • 704
  • 6
  • 16
  • 1
    i feel like there is not enough information provided to determine that there will be a full table scan – Jonesopolis Nov 07 '16 at 15:17
  • 1
    It will not perform a [table scan](https://en.wikipedia.org/wiki/Full_table_scan), that's something else entirely. That would be related to the `Where` clause not the `select`. – Liam Nov 07 '16 at 15:17
  • It's not because you query all columns that you do a table scan. Note that OP is not querying all rows. – Kris Vandermotten Nov 07 '16 at 15:17
  • ahh true, I didnt pay attention to the Where clause. rushed my answer – Stormhashe Nov 07 '16 at 15:20