查询优化摆脱Order by

Question

在SQL Server实例上，我有以下表格：

CREATE TABLE [dbo].[DIALPLAN](
    [IdDialPlan] [int] IDENTITY(1,1) NOT NULL,
    [Prefixe] [varchar](500) NOT NULL,
    [Type] [varchar](255) NOT NULL,
    [Country] [varchar](255) NOT NULL,
    [Description] [varchar](255) NOT NULL,
 CONSTRAINT [PK_TMP_DIALPLAN] PRIMARY KEY CLUSTERED 
(
    [IdDialPlan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [CK_TMP_DIALPLAN_UNIQUE] UNIQUE NONCLUSTERED 
(
    [Prefixe] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CALLS](
    [uniqueid] [varchar](150) NULL,
    [accountcode] [varchar](20) NULL,
    [OutTel] [varchar](80) NULL,
    [lastapp] [varchar](80) NULL,
    [lastdata] [varchar](200) NULL,
    [CallLocalTime] [datetime] NULL,
    [answer] [datetime] NULL,
    [CallEnd] [datetime] NULL,
    [duration] [int] NULL,
    [CallDuration] [int] NULL,
    [disposition] [varchar](20) NULL,
    [Destination] [text] NULL,
    [Asterisk] [varchar](20) NULL,
    [Endreason] [varchar](20) NULL,
    [CLI] [varchar](80) NULL
)

DIALPLAN表包含所有电话号码前缀(100K行)，字段Prefixe包含前缀值(例如，对于加拿大艾伯塔省为1403，对于法国移动布伊格为33665 )，而CALLS表包含电话呼叫(始终选择一批1K行)。以下查询的目的是查找每个电话号码的正确前缀( CALLS表的字段OutTel )。右边的前缀是与电话号码匹配的最长前缀：

-- Solution 1 4200ms : Sort 40%  Top 10%
SELECT TOP 1000 SUBSTRING(OutTel,3,LEN(OutTel)), FN.IdDialPlan, FN.Description
  FROM [dbo].[CALLS]
  CROSS APPLY (SELECT TOP 1 IdDialPlan, Description FROM [dbo].[DIALPLAN] dp WHERE dp.Prefixe = LEFT(SUBSTRING(OutTel,3,LEN(OutTel)),LEN(dp.Prefixe)) ORDER BY LEN(Prefixe) DESC) AS FN


-- Solution 2 3400ms : Sort(Join) 50%
SELECT TOP 1000 SUBSTRING(calls.OutTel,3,LEN(calls.OutTel)), dp.IdDialPlan, dp.Description
FROM [dbo].[CALLS] calls LEFT JOIN [dbo].[DIALPLAN] dp ON dp.Prefixe = LEFT(SUBSTRING(calls.OutTel,3,LEN(calls.OutTel)),LEN(dp.Prefixe))

有没有其他方法可以在更短的时间内得到相同的结果？

编辑1：

这是解决方案1的执行计划：

​

​

这是解决方案2的执行计划：

​

​

编辑2：

下面是一些示例数据

CALLS表OutTel字段仅用于显示目的

OutTel
----------------
0033170448508
0033155373050
0021620383555
0016465699156
00390689971917

DIALPLAN表

IdDialPlan  Prefixe         Type     Country         Description
----------- --------------- -------- --------------- ------------------------------
471         1646            Fixed    United States   United States - New York
32284       331             Fixed    France          France - Paris City
32318       3317044         Fixed    France          France - Paris City
34658       216             Fixed    Tunisia         Tunisia
34659       21620           Mobile   Tunisia         Tunisia - Mobile - Tunisiana
35571       3906            Fixed    Italy           Italy - Rome

谢谢。

Answer 1

看起来你需要做一些结构上的改变，因为你的索引总是因为函数的连接而被搜索，而那些排序是你最常用的运算符。

你可以尝试使用max()而不是top 1，但是你在那里有描述，所以它不能工作。

您可以使用聚集索引创建一个映射表，该映射表将通过填充您的两个表(希望它是一个存储过程而不是一个实体框架)来填充，因此它将预先对您的列进行排序，但它会减慢您的插入和更新。它还可以解决您的索引扫描操作。