c# - 如何提高此 Json 数组的 SQL 性能?
问题描述
在我的 SQL Server 2016+ 中,我有一个带有 json 列的表,json 列具有以下格式:
{
"$type": "Sample.Product, Sample",
"Name": "sample",
"Id": "12345",
"Policies": {
"$type": "System.Collections.Generic.List`1[[Sample.Policy, Sample]], mscorlib",
"$values": [
{
"$type": "Sample.ListPricingPolicy, Sample",
"Prices": {
"$type": "System.Collections.Generic.List`1[[Sample.Money, Sample]], mscorlib",
"$values": [
{
"$type": "Sample.Money, Sample",
"CurrencyCode": "USD",
"Amount": 49.9900
}
]
},
"PolicyId": "af5617ad5a2146a981a5db46ecae60be",
"Models": {
"$type": "System.Collections.Generic.List`1[[Sample.Model, Sample]], mscorlib",
"$values": []
}
},
{
"$type": "Sample.PriceCardPolicy, Sample",
"PriceCardName": "PriceCard-12345",
"PolicyId": "c34c61051d59459fb14e057bead2d128",
"Models": {
"$type": "System.Collections.Generic.List`1[[Sample.Model, Sample]], mscorlib",
"$values": []
}
}
]
}
}
在上述数据中:
- 主要类型可能不是
Sample.Product, Sample
, Policies.$values
是一个通用列表,可以是任何类型的Policy
PriceCardPolicy
某些json数据中可能不存在
我需要过滤所有具有PriceCardPolicy
.
我当前的 SQL 是:
SELECT s.*
FROM dbo.product s
WHERE
json_value(s.Data, '$."$type"') = 'Sample.Product, Sample'
AND NOT EXISTS -- filter all those who have `PriceCardPolicy`
(
SELECT e.*
FROM dbo.product e
CROSS apply Openjson(e.Data, '$.Policies."$values"') WITH(policytype nvarchar(max) '$."$type"') policies
WHERE
json_value(e.Data, '$."$type"') = 'Sample.Product, Sample'
AND policies.policytype = 'Sample.PriceCardPolicy, Sample'
AND s.id = e.id)
但是当数据增长时,它的性能真的很差,比如 500K 行。我该如何改进呢?
例如:
有 500k 行,但只有 5 行没有PriceCardPolicy
. 我需要找出那 5 行。
我无法对数据库进行任何更改,我只能专注于改进此 SQL。谢谢。
解决方案
如果您需要在 json 中进行搜索,则需要创建计算列并对其进行索引,或者将其他表中的值切碎并正确索引。
例如:
ALTER TABLE dbo.product ADD json_datatype as json_value(s.Data, '$."$type"');
CREATE INDEX idx_product_json_type ON dbo.product (json_datatype);
推荐阅读
- python - ValueError: int() 以 10 为底的无效文字:'(?)' Dash Plotly Pandas
- tableau-api - 桌面提取时间在服务器中需要一个多小时,而在桌面中需要几秒钟
- html - 如何取消“li:before {content:'text'}”的继承而不为每个重新分配它
- 元素?
- javascript - 用户运行托管 IIS 链接时如何在 asp.net 中获取客户端 IP 地址
- flutter - NoSuchMethodError:在 null 上调用了 getter 'dlongitude'
- javascript - 使用 React 播放音频
- ffmpeg - 将 5.1 PRORES 转换为 Prores 立体声
- javascript - react js在哪里存储网站的图像?
- javascript - 获取数组中字符串的最后一个字符
- laravel - 如何将 laravel 应用程序同时用作 Web 应用程序和 Rest API