查询优化以将VARBINARY转换为VARCHAR并在其上进行charindex

Question

我有一个存储库表，它有大约1870万行，每个月添加大约50万到10万行。表格结构如下

CREATE TABLE [dbo].[my_table](
    [id] [bigint] NULL,
    [a_timestamp] [datetime] NULL,
    [eventId] [bigint] NULL,
    [userId] [varchar](255) NULL,
    [customerid] [varchar](128) NULL,
    [messageType] [varchar](100) NULL,
    [message] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我编写了以下查询，以获取每个月的各种计数。该查询现在大约需要10分钟才能执行。我需要帮助来优化这个查询，如果可能的话，把时间缩短到几分钟。

SELECT DATEADD(month, DATEDIFF(month, 0,a_timestamp), 0) AS MonthYear, 
       COUNT(*) AS [Count], 
       COUNT(DISTINCT customerid) AS [Unique Customers], 
       COUNT(DISTINCT userId) AS  [Unique Users]
FROM [my_table]
WHERE messageType = 'Outbound'
AND userId NOT IN ('master', 'admin')
AND CHARINDEX('Retrieve Document',CONVERT(VARCHAR(MAX),[message])) > 1
GROUP BY DATEADD(month, DATEDIFF(month, 0,a_timestamp), 0) 
ORDER BY MonthYear

我认为执行时间长的主要原因如下

• CHARINDEX('Retrieve Document',CONVERT(VARCHAR(MAX),[message])) > 1从VARBINARY转换为VARCHAR并搜索"Retrieve Document"
• userId NOT IN ('master', 'admin')过滤列表中用户以外的用户（实际列表长度大于2个字符串，大约为10个字符串）
• 表中1870万行

需要注意的几点

• 我不创建此表，也无法更改它
• 我没有SHOWPLAN权限
• 我需要在Excel数据连接中使用此查询，并让用户从Excel运行它。用户将只有选择权限

Answer 1

鉴于您无法更改现有的表，最好更改您的策略。而不是每次都运行查询并完全构建一组新的结果。为什么不每月将新结果插入另一个表（我们称之为AccumulatedResults）中呢。

这样，您每次只处理50万条新记录。这将比每次重建整个结果集快得多。查询看起来有点像：

INSERT INTO AccumulatedResults
(
  MonthYear,
  [COUNT],
  UniqueCustomers,
  UniqueUsers,
)  
SELECT
  DATEADD(month, DATEDIFF(month, 0, a_timestamp), 0) AS MonthYear,
  COUNT(*) AS [Count],
  COUNT(DISTINCT customerid) AS [Unique Customers],
  COUNT(DISTINCT userId) AS [Unique Users]
FROM
  [my_table]
WHERE
  messageType = 'Outbound' AND
  userId NOT IN ('master', 'admin') AND
  CHARINDEX('Retrieve Document', CONVERT(VARCHAR(MAX), [message])) > 1
  -- This is a new condition
  AND DATEADD(month, DATEDIFF(month, 0, a_timestamp), 0) 
  > (SELECT MAX(MonthYear) FROM AccumulatedResults)
GROUP BY
  DATEADD(month, DATEDIFF(month, 0, a_timestamp), 0)