一对一连接在SQL Server中不够快 [英] One to one join Not fast enough in SQL Server

问题描述

我的EF模型中有TPT继承。有几种类型继承的主抽象类型，包括顺序。有1700000个订单，但主人有更多的行对应其他类型。

我们有一个奇怪的情况，其中选择50个订单比选择相同的50个订单慢，但包括一些其他相关实体。它追踪到数据库，其中非常简单的查询

 从SAM.Master m 
中选择top 50 *加入SAL。 [订单] o on.OrderMasterID = m.MasterID 
通过MasterID desc 
  

超过一秒钟。 （是的，在我们的例子中，有一秒其实太多了）。但是，可以通过

1. 删除（<2）更快）


2. 按升序进行排序（聚簇索引以升序排列，不能以其他方式）


3. 添加循环加入）（极快）


4. 使用左外连接


5. 添加其中FormTypeID = 1 （主表中的discriminator列为所有订单为1）（两倍快）

实际上产生相同结果的唯一解决方案是3和5，但3是不可能使用实体框架（我们不能添加提示查询）和5不够快[/ p>

<

解决方案

您可以使用计划指南以获得您所期望的行为。请参阅在计划指南中使用查询提示举个例子该示例需要实际的语句文本（由EF生成的T-SQL），但您可以规避使用 sp_create_plan_guide_from_handle 。

在您的下一个项目中，避免使用基于的类表继承 Master 对象，每个实体派生自...

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

1. Removing order by (about two times faster)
2. sorting in ascending order (clustered indices are in ascending and can't be otherwise)
3. adding option(loop join) (extremely fast)
4. using left outer join
5. 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 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...

这篇关于一对一连接在SQL Server中不够快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！