👀 Views: 58 💬 Answers: 1 📅 Created: 2025-06-13

sql-server json performance sql-server-2019 SQL

I tried several approaches but none seem to work. I'm currently working with a performance scenario while trying to update large JSON objects stored in a SQL Server 2019 table using the `JSON_MODIFY` function. The JSON data can get quite large, sometimes exceeding 1MB in size, and I need to update specific fields within it. Here's a simplified example of what I'm trying to do: ```sql DECLARE @json NVARCHAR(MAX); SET @json = (SELECT TOP 1 JsonColumn FROM MyTable WHERE Id = 1); SET @json = JSON_MODIFY(@json, '$.newField', 'newValue'); UPDATE MyTable SET JsonColumn = @json WHERE Id = 1; ``` When I execute this, the update takes an unusually long time, sometimes resulting in timeout errors. I have also noticed that the execution plan shows a lot of logical reads, suggesting that it might be scanning more rows than necessary. To troubleshoot, I tried creating an index on the JSON column using a computed column, but that didn't help much. Additionally, if I try to log the execution time, I see that the calling of `JSON_MODIFY` takes up a important portion of the overall duration, especially if the JSON structure is nested or complex. I’m looking for recommendations on optimizing the performance of updating large JSON objects or any best practices that I might be missing. Has anyone experienced similar issues or found effective solutions to handle this scenario? Thanks in advance for your help! My development environment is Windows.