使用 BETWEEN 条件对 INNER JOIN 进行性能调整

2021-12-06 00:00:00 azure-sql-database azure sql sql-server

我有两个表，分别是 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)

相关文章