CODEWITHSUNDEEP
sqlindexing
TheGeneral
TheGeneral

Reputation: 81493

Best SQL In-Memory OLTP Index type for Like operator, Hashed vs Non-Hashed

Im considering an In-Memory OLTP implementation on a table i have with 5+ million records

I use the following query and it runs at about 5 ms, however it would be good if i can get even better performance

  SELECT TOP 1 ClassId 
    FROM Exchanges 
    where ExchangeSetId = 1 
       and BasePrefix like left(@BaseNumber, 3) + '%' 
       and @BaseNumber like BasePrefix +'%' 
       and @DestNumber like DestPrefix +'%'

The above query was obtained from this background question I've asked

SQL Server Performance tips for like %

There are currently indexes on ExchangeSetId, BasePrefix, DestNumber to speed up performance, the below index where recommended by the tuning adviser, scripted as follows

USE [JCE]
GO

/****** Object:  Index [ExchangeIdx]    Script Date: 5/06/2014 3:55:35 PM ******/
CREATE NONCLUSTERED INDEX [ExchangeIdx] ON [dbo].[Exchanges]
(
    [ExchangeSetId] ASC,
    [BasePrefix] ASC,
    [DestPrefix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

However i'm confused as to what index method i should use in the optimization migration adviser, screen-shot supplied

enter image description here

Question, is it worth while even using OLTP in the situation?

If so, what is the appropriate index type that would work best with like operator shown in the query above?

Edit

My intuition tells me it should be the non hashed index, however i don't know the internals of SQL enough to prove it

Upvotes: 1

Views: 182

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20842

When I can't prove something with a thought experiment, or by applying knowledge, then I do one thing, I measure.

But for the sake of an answer:

I'm assuming that your ExchangeSetId is a field with a very low cardinality.

My gut feeling is that even though you only need 1 record returned in the result, using TOP 1, you still use 2 range predicates to get there, so I'd avoid the HASH index and also avoid using the lowest cardinality field as the leading field of the index. Try a regular NONCLUSTERED index, or a CLUSTERED index, keeping the 2 fields that are in the range predicates (BasePrefix and DestPrefix) on the front of the index order.

5ms isn't bad performance for a query, anyway, so you need to decide how you are measuring success. You need to create a baseline: an ideal test case that actually has 5 million similar records, except the 3 key fields make up a UNIQUE index, and query it without wildcards, to get a single row, and measure how fast that case would return a record in your database (using the OLTP in memory option and one or both of those index types). Measure that. That represents the best you could every do. If the results are sub 1 millisecond, then maybe your turning exercise is worthwhile, but if not, then maybe there isn't as much latency gain to be had as you'd like, and you then need to decide if measuring success by latency is the only thing that matters here, or if you should be measuring increased concurrency (which is another benefit of the OLTP in memory option). After that, if you don't need increased concurrency, then maybe there isn't a need after all.

Upvotes: 1

Related Questions