Click here to Skip to main content
14,304,152 members
Rate this:
Please Sign up or sign in to 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
Updated 23-Oct-18 1:56am
v2
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

Rate this:
Please Sign up or sign in to vote.

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[^]
   
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
   
Ok any 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 :)

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100