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
json
sql-server performance asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
add a comment |
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
json
sql-server performance asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
add a comment |
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
json
sql-server performance asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
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
json
sql-server performance json
sql-server performance asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
edited Aug 9 '18 at 5:27
NeroIsNoHero
asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
asked Aug 9 '18 at 5:02
NeroIsNoHeroNeroIsNoHero
838
838
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The combination of a computed column and an index may help.
ALTER TABLE [HourlySnapshot]
ADD [BuildingMeterId] AS JSON_VALUE([MeterData], '$.BuildingMeterId');
CREATE NONCLUSTERED INDEX IX_ParsedBuildingMeterId ON [HourlySnapshot] (BuildingMeterId)
This actually causes SQL Server to parse and index the value at insert/update time. When reading, it can use the index and not do a full table scan.
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51759156%2fhow-to-improve-performance-of-a-t-sql-query-that-has-openjson-to-filter-json-arr%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The combination of a computed column and an index may help.
ALTER TABLE [HourlySnapshot]
ADD [BuildingMeterId] AS JSON_VALUE([MeterData], '$.BuildingMeterId');
CREATE NONCLUSTERED INDEX IX_ParsedBuildingMeterId ON [HourlySnapshot] (BuildingMeterId)
This actually causes SQL Server to parse and index the value at insert/update time. When reading, it can use the index and not do a full table scan.
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
add a comment |
The combination of a computed column and an index may help.
ALTER TABLE [HourlySnapshot]
ADD [BuildingMeterId] AS JSON_VALUE([MeterData], '$.BuildingMeterId');
CREATE NONCLUSTERED INDEX IX_ParsedBuildingMeterId ON [HourlySnapshot] (BuildingMeterId)
This actually causes SQL Server to parse and index the value at insert/update time. When reading, it can use the index and not do a full table scan.
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
add a comment |
The combination of a computed column and an index may help.
ALTER TABLE [HourlySnapshot]
ADD [BuildingMeterId] AS JSON_VALUE([MeterData], '$.BuildingMeterId');
CREATE NONCLUSTERED INDEX IX_ParsedBuildingMeterId ON [HourlySnapshot] (BuildingMeterId)
This actually causes SQL Server to parse and index the value at insert/update time. When reading, it can use the index and not do a full table scan.
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
The combination of a computed column and an index may help.
ALTER TABLE [HourlySnapshot]
ADD [BuildingMeterId] AS JSON_VALUE([MeterData], '$.BuildingMeterId');
CREATE NONCLUSTERED INDEX IX_ParsedBuildingMeterId ON [HourlySnapshot] (BuildingMeterId)
This actually causes SQL Server to parse and index the value at insert/update time. When reading, it can use the index and not do a full table scan.
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
answered Dec 31 '18 at 14:02
John HovenJohn Hoven
3,62022229
3,62022229
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51759156%2fhow-to-improve-performance-of-a-t-sql-query-that-has-openjson-to-filter-json-arr%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
