Queries to in Memory table are slower than to disk based

Question

I'm working with an already built table which I must migrate to a memory optimized one. The following is a clone from the disk based. Both have the same structure with the exception that the disk based table has no primary key even though the OID field should not repeat.

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)

As every memory optimized table has to have, at least, one index and the columns are foreign keys to other tables, I have made these columns hash indexes having calculated the BUCKET_COUNT as indicated on the web:

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

Nevertheless, queries such as simple selects with/out group by are slower than the ones executed against the original table. I've tried also with nonclustered indexes and increasing the bucket count, but still the same.

How could I make queries speed up?

I suggest you ask this question on https://dba.stackexchange.com/ — Alex, Jul 07 '17 at 10:09

score 0 · Answer 1 · answered Feb 20 '19 at 12:18

You should add

OPTION (RECOMPILE)

at the end of your statement :

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 is not really optimized for this as it compiles its plan when creating the variable table (with no record). When you fill it with data the compiled plan is not optimized because there were no data at first compile time.

When you add this option it tells SQL Server to recompile keeping in mind that there are datas now.

It should be way faster.

Queries to in Memory table are slower than to disk based

1 Answers1