Index on nvarchar column doesnot affect performance

Question

1.00/5 (1 vote)

See more:

Hello,

I have a column in table that have nvarchar(200) datatype

and then I add index on another column include my nvarchar(200) column

but the performance is the same when I do a select query with where condition in the nvarchar(200) column

I need to know why the index on nvarchar column doesn't affect the performance?

What I have tried:

CREATE TABLE dbo.POS_JSON_Transaction_2000
(
  Id INT IDENTITY PRIMARY KEY,
  [Name] Nvarchar(50),
  [Transaction] nvarchar(2000) 
);

CREATE INDEX POS_JSON_Transaction_2000_Transaction_Index ON 
POS_JSON_Transaction_2000(Name) include ([Transaction]);

select * from POS_JSON_Transaction_2000 where [Transaction] like '%"NAME__STRING":"testKA"%'

Posted 23-Oct-18 1:19am

Heba Kamel

Updated 23-Oct-18 1:56am

v2

Add a Solution

Comments

#realJSOP 23-Oct-18 7:22am

You should ask this question in one of the forums. It's more of a discussion that a specific question.

Richard Deeming 23-Oct-18 7:34am

We can't see your table; we can't see your index; we can't see your query; and we can't see your data. Any one of those could affect the query plan.

Click the green "Improve question" link and update your question to describe the relevant parts of your table structure, the index definition, and the query you're running.

Heba Kamel 23-Oct-18 7:44am

ok, I have updated the question
Thanks

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-10-23T01:56:00

Solution 1

There are a couple of problems here.

Firstly, the index is on the Name column. It can be used to satisfy queries on the Name column, but will be of no use for queries on the Transaction column.

Think of it an address book which is sorted by last name. You have created a secondary index based on the first name, and included the town in that index. You now want to find all entries in a specific town. The secondary index wouldn't help, since you'd still have to go through every entry to find the matching ones.

The other problem is that you're using a "contains" query - WHERE column LIKE '% ... %'. There is no sensible way for an index to help with that query. Again, given a list of towns in alphabetical order, if you want to find any town which contains the letter "p", you still have to go through every entry to find it.

If you were performing a "starts with" query - WHERE column LIKE '... %' - then an index could help.

Even for an "ends with" query - WHERE column LIKE '% ...' - it would be possible to use an index.

But for a "contains" query, there's not much you can do, short of creating a separate table with every substring from your column.

Sargability: Why %string% Is Slow - Brent Ozar Unlimited®[^]
One way to get an index seek for a leading %wildcard in SQL Server[^]

Posted 23-Oct-18 1:56am

Richard Deeming

Comments

Heba Kamel 23-Oct-18 8:15am

" the index is on the Name column. It can be used to satisfy queries on the Name column, but will be of no use for queries on the Transaction column.
"

I did the index on Name column with include Transaction column
because of I cannot create the index directly on Transaction column because it's nvarchar(2000)

Richard Deeming 23-Oct-18 8:18am

And as I explained, an index on the Name column won't help you find the records with a particular value in the Transaction column. It will only help you find records with a particular Name. Including the Transaction column just means that SQL doesn't have to go back to the original record if it needs to read the Transaction column for a record it's found using that index.

And even if you had created an index on the Transaction column, it wouldn't help you with a "contains" query.

Heba Kamel 23-Oct-18 8:23am

Okany suggestions to do this ?
for me the whole string must put in one column.

Richard Deeming 23-Oct-18 8:28am

Have a look at the second link in my answer. It will require a lot more space, and make adding or updating records a lot slower; but it's about the only way to get an index working on a "contains" query.

Based on the text you're searching for, I'm guessing you're storing multiple values in a single column? If so, you should really consider splitting them out into a separate table, and searching on that. Alternatively, if you're using SQL Server 2016, you could try storing the data as JSON and creating an index on that[^].

Heba Kamel 23-Oct-18 8:27am

because it's a JSON object, and the properties is dynamic

Richard Deeming 23-Oct-18 8:29am

Index JSON data | Microsoft Docs[^]

Requires SQL Server 2016 or 2017.

Richard Deeming 23-Oct-18 8:38am

You're good to go then. Read the Microsoft article I linked to, which explains how to create a computed column to extract a value from your JSON data, create an index on that column, and then query that column instead of the raw JSON text.

Heba Kamel 23-Oct-18 8:49am

ok, Thanks for your support
I'll try :)

Index on nvarchar column doesnot affect performance

1 solution

Solution 1

Add your solution here

Preview 0

Index on nvarchar column doesnot affect performance

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us