sql-server - 查询内存表比基于磁盘的查询慢

Question

我正在使用一个已经构建的表，我必须将它迁移到一个内存优化的表。以下是基于磁盘的克隆。两者具有相同的结构，除了基于磁盘的表没有主键，即使 OID字段不应重复。

CREATE TABLE [dbo].[DATA_IM](
    [OID]       [varchar](36) NOT NULL, -- NEWID()
    [YEAR]      [varchar](15) NOT NULL  INDEX hash_sce HASH (YEAR) WITH (BUCKET_COUNT = 128),
    [MONTH]     [varchar](2)  NOT NULL,
    [DEPARTMENT][varchar](30) NOT NULL  INDEX hash_dep  HASH (DEPARTMENT)  WITH (BUCKET_COUNT = 64),
    [ACCOUNT]   [varchar](30) NOT NULL  INDEX hash_acct HASH (ACCOUNT)     WITH (BUCKET_COUNT = 2048),
    [DIM1]      [varchar](30) NULL      INDEX hash_de1  HASH (DIM1) WITH (BUCKET_COUNT = 256),
    [DIM2]      [varchar](30) NULL      INDEX hash_de2  HASH (DIM2) WITH (BUCKET_COUNT = 256),
    [DIM3]      [varchar](30) NULL      INDEX hash_de3  HASH (DIM3) WITH (BUCKET_COUNT = 256),
    [DIM4]      [varchar](30) NULL      INDEX hash_de4  HASH (DIM4) WITH (BUCKET_COUNT = 256),
    [DIM5]      [varchar](30) NULL,
    [CATEGORY]  [varchar](30) NOT NULL INDEX hash_cat HASH (CATEGORY) WITH (BUCKET_COUNT = 256),
    [VALUE]     [numeric](27, 9) NOT NULL,
    [CURRENCY]  [varchar](5)     NULL,
    [ORIGIN]    [varchar](80)   NULL,
    [USERUPD]   [varchar](255)  NULL,
    [DATEUPD]   [datetime]      NULL,
    [NOTE]      [varchar](1000) NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

由于每个内存优化表都必须至少有一个索引，并且列是其他表的外键，因此我已经计算了 BUCKET_COUNT 使这些列散列索引。如网上所示:

with cte1 AS (select count(distinct year)        year from data),
     cte2 AS (select count(distinct department)  dept from data)

SELECT  POWER(2, CEILING(LOG(year) / LOG(2))) AS [year],
        POWER(2, CEILING(LOG(dept) / LOG(2))) AS [dept]

FROM cte1, cte2

尽管如此，诸如简单选择之类的查询会使用/退出 group by比对原始表执行的要慢。我也尝试过使用非聚集索引并增加桶数，但仍然相同。

如何加快查询速度？

Answer 1

你应该添加

OPTION (RECOMPILE)

在你的陈述结束时:

with cte1 AS (select count(distinct year)        year from data),
     cte2 AS (select count(distinct department)  dept from data)

SELECT  POWER(2, CEILING(LOG(year) / LOG(2))) AS [year],
        POWER(2, CEILING(LOG(dept) / LOG(2))) AS [dept]

FROM cte1, cte2
OPTION (RECOMPILE)

SQL server 并没有真正为此优化，因为它在创建变量表(没有记录)时编译了它的计划。当你用数据填充它时，编译计划没有优化，因为在第一次编译时没有数据。

当您添加此选项时，它会告诉 SQL Server 重新编译，记住现在有数据。

它应该更快。