在条件为条件的情况下对INNER JOIN进行性能调整 [英] Performance tuning on INNER JOIN with BETWEEN Condition

问题描述

我有两个表，分别是tbl_Small和tbl_Large.

I have two table's namely tbl_Small and tbl_Large.

这两个表都存储在 Microsoft Azure 中，并从 Microsoft SQL Server 查询.

Both the table's I have stored in Microsoft Azure and querying from Microsoft SQL Server.

-表1:Tbl_Small

--Table 1: Tbl_Small

CREATE TABLE tbl_Small
(
    cola int
);

INSERT INTO tbl_Small VALUES(1234),(123),(34); 
--1000 rows

-表2:tbl_Large

--Table 2: tbl_Large

CREATE TABLE tbl_Large
(
    ID bigint identity(1,1),
    cola int,
    colb int,
    colc varchar(100)
);

INSERT INTO tbl_Large(cola,colb,colc) VALUES(0,140,'A'),(150,200,'C'),(1000,15000,'D');
--30 million rows 

我想通过在条件之间加入小表来获取大表的详细信息.

I want to get large table details by joining small table with between condition.

我的尝试:

1. 在tbl_Small(cola)上创建了NONCLUSTERED索引.
2. 在tbl_Large(cola)和tbl_Large(colb)上创建了NONCLUSTERED索引.

1. Created NONCLUSTERED index on tbl_Small(cola).
2. Created NONCLUSTERED index on tbl_Large(cola) and tbl_Large(colb).

查询:

SELECT s.cola as [Input],l.cola,l.colb,l.colc
FROM tbl_Large AS l
INNER JOIN tbl_Small s ON s.cola BETWEEN l.cola and l.colb

注意:以上查询的执行时间超过10分钟.

Note: The above query's execution time is over 10 minutes.

编辑:按照回答所述，在所有列上添加了非聚集索引之后，我得到了以下执行计划.

Edit: After adding nonclustered index on all columns as said in answer, I got the following execution plan.

执行时间:5分钟

DTU百分比图:

推荐答案

您在tbl_Large上的索引需要覆盖，即它包含查询所需的所有数据.如果仅在一个列上创建索引，则服务器将需要使用索引，而另一个源来获取另一列数据，以获取所有数据.很有可能会发现它不值得进行额外的工作，并且会完全忽略索引.

Your index on tbl_Large needs to be covering i.e. it holds all the data the query needs. If you just create an index on the one column then to get all the data the server will need to use the index and another source to get the other column data. It's probable it won't find it worth the extra work and will ignore the index all together.

对于tbl_Large，在col a和col b上都创建一个索引，并同时包含col c的值，因此代码如下所示:

For tbl_Large create an index on both col a and col b and also include the value for col c so the code looks like this:

CREATE NONCLUSTERED INDEX IX_tbl_Large_cola_colb on tbl_Large (cola, colb)
INCLUDE (colc)

这篇关于在条件为条件的情况下对INNER JOIN进行性能调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！