CODEWITHSUNDEEP
jsonsql-serverperformance
NeroIsNoHero
NeroIsNoHero

Reputation: 293

How to improve performance of a T-SQL query that has OPENJSON to filter JSON array properties

I have a table that contains a JSON array column (nvarchar(max)), has millions of rows expected to be billions of rows in the future.

The table structure is like this:

[SnapshotId] - PK,
[BuildingId],
......................
[MeterData],

MeterData contains Json array like this:

[{
    "MeterReadingId": 0,
    "BuildingMeterId": 1,
    "Value": 1.0,   
}, {
    "MeterReadingId": 0,
    "BuildingMeterId": 2,
    "Value": 1.625, 
}]

I need to filter by "HourlySnapshot" table where "BuildingMeterId = 255" is for example, wrote the below query

SELECT * 
FROM [HourlySnapshot] h 
CROSS APPLY OPENJSON(h.MeterData) 
    WITH (BuildingMeterId int '$.BuildingMeterId') AS MeterDataJson
WHERE MeterDataJson.BuildingMeterId = 255

Works fine, but performance is bad due to parse of JSON. I read you can overcome the performance issue by creating indexes. I created a clustered index like below

CREATE CLUSTERED INDEX CL_MeterDataModel 
    ON [HourlySnapshot] (MeterDataModel)

But can't see any improvements in terms of speed. Have I done it wrong ? what is the best way to improve the speed.

Thanks

NeroIsNoHero

Upvotes: 5

Views: 4432

Answers (0)

Related Questions