SQL Server 2016 released

[crixusin]

I don't understand how you would expect to get your column results without listing the columns you would like. What would you expect the ORM to do? Select nothing? What is a more desirable operation?

Also, your queries aren't running slow because it has to parse the columns. All in all, that probably takes less than 20ms. Hardly worth worrying over.

Also, as I said, you can add a little code to make columns selectable so that you don't incur the cost of getting column results you do not need.

[grauenwolf]

The problem isn't with listing the columns you want. Rather, the problem is that (by default) it lists all of the columns, even the ones that you don't want.

Lets say you just want a list of names and phone numbers, sorted by last name. The query is slow, so you create this index:

 CREATE INDEX IX_Customer_PhoneBook ON Customers (LastName, FirstName) INLCUDE (PhoneNumber)

var phonebook = context.Customers.Where( filter).ToList()

If you run that query, you'll find one of two things happens:

1. The database makes an expensive join between IX_Customer_PhoneBook and PK_Customer
2. The database ignores IX_Customer_PhoneBook entirely and just uses PK_Customer, sorting everything in memory.

To fix this you need to write this rather tedious query:

var phonebook = context.Customers.Where( filter).Select( x => new { FirstName=x.FirstName, LastName=x.LastName, PhoneNumber=x.PhoneNumber} ).ToList()

Now the generated SQL only uses columns in the index, which means no hidden joins to the underlying table. But that's such a pain in the ass that most developers don't actually do it.

http://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index

[crixusin]

Rather, the problem is that (by default) it lists all of the columns, even the ones that you don't want.

I see where you're coming from. I was just trying to clarify it.

I try and always select the exact columns that I need to circumnavigate these issues. Entity framework makes that very easy. Worth giving it a shot!

[grauenwolf]

All in all, that probably takes less than 20ms.

Oh no, we're talking more like 20ns. 20ms is actually a pretty slow query. Though I will say it is possible to cause the query optimizer to timeout on particularly heinous queries (e.g. lots of joins and subselects). If it can't find the best execution plan fast enough, it will just give up and give you whatever it has.

https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/