0

I want to improve the performance of the query var dbObj = dbSet.Where(x => x.Name == name).FirstOrDefault(); done on the table pasted below. I was thinking about adding an index on [Name]. What type of index should I use if I don't need the ability to query for substrings?

CREATE TABLE [dbo].[TitleTypes] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (MAX) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
    CONSTRAINT [PK_TitleTypes] PRIMARY KEY CLUSTERED ([Id] ASC)

)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[TitleTypesHistory]));

I'm looking into Full Text Search which requires the creation of a catalog. If I understand my research correctly this would greatly benefit someone who needs to search substrings within the text which is not the case here. Instead I was hoping for something like an index of a strings binary to make it quicker to determine if my search string is contained within the column.

6
  • 6
    Are your Name values 2 billion characters?? If not, specify a max size to facilitate creating an index on the column.
    – Dan Guzman
    Jan 11 at 16:52
  • 2
    I don't think you can index a varchar(max) column; does it really have no practical limit? See here.
    – mustaccio
    Jan 11 at 16:53
  • 3
    What type of index should I use if I don't need the ability to query for substrings? (1) You need in regular index. (2) Index usage for long string-type columns may be not effective. I recommend you to add generated column which stores some hash of the value (use HASHBYTES() function) and create index by this column.
    – Akina
    Jan 11 at 17:04
  • 7
    As long as you have a max data type for name you’re going nowhere. Jan 11 at 17:15
  • 1
    My answer here should work for your question dba.stackexchange.com/questions/301186/… Jan 12 at 19:14
0

For wide character columns your best bet is to calculate a checksum and add an index.

Then calculate the checksum of what you are searching for and add that to your query, it looks like this:

-- add a persisted computed column, we will calculate a checksum over the body column which is of type nvarchar(max) alter table dbo.posts add chksum_body as checksum(body) persisted

-- add an nonclustered index to the table for the new column create nonclustered index ncix_posts_chksum_body on dbo.posts (chksum_body)

-- show query io and cpu usage set statistics io, time on

-- we need 2 variables, one for the string we are searching for and one for its checksum declare @str nvarchar(max) declare @chksumstr int

-- initialise the variable with the search string select @str = N'Can anyone help me to index wide columns in SQL Server please' -- calculate a checksum for the search string and store in a variable select @chksumstr = checksum(@str)

-- compare search the old way (no index) select top 100 * from [dbo].[Posts] where body = N'Can anyone help me to index wide columns in SQL Server please'

-- to search using the checksum, note we recheck the value of the body to eliminate any false matches due to checksum collisions -- but as we have an index seek we just are doing a residual filter on the few matching rows select top 100 * from [dbo].[Posts] where body = @str and chksum_body = @chksumstr SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

Id AcceptedAnswerId AnswerCount Body 72 0 2 Can anyone help me to index wide columns in SQL Server please (1 row affected)

Table 'Posts'. Scan count 5, logical reads 4239877, physical reads 2150, page server reads 0, read-ahead reads 4194084, page server read-ahead reads 0, lob logical reads 677199, lob physical reads 407176, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times: CPU time = 34829 ms, elapsed time = 36995 ms.

Id AcceptedAnswerId AnswerCount Body 72 0 2 Can anyone help me to index wide columns in SQL Server please (1 row affected)

Table 'Posts'. Scan count 1, logical reads 7, physical reads 4, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

0

I would suggest to evaluate the MAX LEN of the [name] field , to see the real usage of it.

You could eventually add a new field with the first 8000 chars of the [name] field and index that field.

But you have definitely to reshape the way you store that data in a searchable way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.