One to one join Not fast enough in SQL Server

Question

I have a TPT inheritance in my EF model. Where there is a "Master" abstract type from which several types inherit, including "Order". There are 1700000 orders, but master has many more rows corresponding to other types.

We had a strange case in which selecting 50 orders was slower than selecting the same 50 orders, but with some other related entities included. It tracked back to database where the very simple query

select top 50 * from SAM.Master m 
join SAL.[Order] o on o.OrderMasterID = m.MasterID
order by MasterID desc

takes more than a second. (Yes, in our case, one second is actually too much). But this can be made faster either by

Removing order by (about two times faster)
sorting in ascending order (clustered indices are in ascending and can't be otherwise)
adding option(loop join) (extremely fast)
using left outer join
Adding Where FormTypeID = 1 (the discriminator column in Master table which is 1 for all orders) (two times faster)

actually the only solution that yielded the same result are 3 and 5, but 3 is impossible using Entity Framework (we can't add hints to queries) and 5 is not fast enough

Any suggestions are greatly appreciated.

You've answered your own question better than any of us could. We'd just be making wild guesses. — Andrew Barber, Apr 08 '12 at 11:44
Thanks, but I really need this type of query (which happens lots of times in my system) to perform very fast. — Alireza, Apr 08 '12 at 11:55
3 can be achieved if you wrap it into a Stored Procedure and call it. However, do you need every column from Master and Order ? This way your indexes would only help so much, since you still get a RID lookup — cairnz, Apr 08 '12 at 15:27

Remus Rusanu · Accepted Answer · 2012-04-08T16:13:25.160

3

You can use plan guides to obtain the behavior you desire. See Using Query Hints in Plan Guides for an example. The example requires the actual statement text (the T-SQL generated by EF) but you can circumvent the need to obtain the EF generated statement by using sp_create_plan_guide_from_handle.

On your next project avoid using Class Table Inheritance with a base Master object that every entity derives from...

edited Apr 08 '12 at 16:13

answered Apr 08 '12 at 16:08

Remus Rusanu

288,378
40
442
569

Thanks, I'll read and try to use your suggestion tomorrow. Btw, we're not inheriting everything from Master, only the masters of the forms we encountered in a specific process. This also happens to details, and we have a set of very interesting tools that work on Master and Detail regardless of their concrete type. If for the sake of avoiding possible problems in performance we want to abandon all benefits of inheritance, I might rather do something very radical, like using an object database, or at least changing to nhibernate (where I can extend the codes to do what I want). – Alireza Apr 09 '12 at 12:13

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

If you want to force a query hint. I think your best shout is to ether create a store procedure and use the query hint (see more here). Or maybe you can do something like this:

var items = dc.ExecuteQuery<ToSomeObject>("YourQueryWithHints").ToList();

edited May 23 '17 at 11:50

Community

1
1

answered Apr 08 '12 at 11:46

Arion

31,011
10
70
88

thanks, but unfortunately this is part of a large system with lots of features that make use of LINQ to entities and using stored procedures will block most features. – Alireza Apr 08 '12 at 11:53
1

Then you might consider using `ExecuteQuery` as some thing in between. Because you can not just query hints in Linq. – Arion Apr 08 '12 at 12:05

One to one join Not fast enough in SQL Server

2 Answers2

Linked