0

So, we have a SQL table named Clients which has the following columns:

  1. UniqueId (nvarchar(200), not null)
  2. Data (nvarchar(max), null) - this one has JSON for each row(aprox. 30k-40k characters)

And we got a request to return to our user, UniqueId using the value from one of the parameters nested in Data JSON field (let's call it Value in this example)

We got something like this:

select UniqueId
from dbo.Clients 
where JSON_VALUE(Data,'$.Parent.Child.ChildOfChild.Value') like 'Value'

But, as expected, table which has 20k+ rows and each row contains JSON in Data(with 30k-40k characters) takes a very long time to execute.

Our question is, is there any way to speed up this kind of query? Given the fact that we can't change the table design. Thanks in advance!

5
  • 2
    Please show the full table and index definitions, also sample data and expected results, and please share the query plan via brentozar.com/pastetheplan. These details are essential to be able to answer a performance question. Commented Nov 20, 2024 at 10:45
  • 1
    There is normally no way to speed this up other than creating a computed column on JSON_VALUE(....) and indexing it. Commented Nov 20, 2024 at 10:46
  • 1
    This question is similar to: Optimize JSON query processing in SQL Server 2016. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. Commented Nov 20, 2024 at 11:03
  • 1
    By storing data as JSON rather than in relational tables, it was decided to make reading and writing easy at the cost of slow searching, if ever needed. Now you do want to search data by JSON and worry about performance? Commented Nov 20, 2024 at 12:28
  • 1
    And when tagging a request with sql always add a tag for your DBMS, too, please. Commented Nov 20, 2024 at 12:29

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.