SQL Server上键/值对表的列存储索引
我需要建立一个数据库来存储评估。
每项评估都可以有不确定数目的问题，也可以有不确定数目的答复。
对每项评估的答复可达500 K。
每次评估的问题可从10到200。
虽然评估和AssessmentResponses表将使用“正常”关系表设计，但问题和答案应作为键/值对存储。
Assessments
|AssessmentID|Name|JsonSchema|
Questions
|QuestionID|AssessmentID|QuestionValue|QuestionType|
AssessmentResponses
|ResponseID|AssessmentID|RespondentName|Date|JsonResponse|
Answers
|ResponseID|QuestionID|AnswerValueText|AnswerValueDecimal|如您所见，我还以JSON格式存储评估模式和响应，因为我发现在web上快速可视化非常有用。特别是JsonResponse包含了在相关响应中给出的所有答案，如下所示：
{
"interviewDate":"2001/12/28",
"city":"Mombasa",
"phone":"123456789",
"name":"Marco",
"age":16
}典型的查询可以是：
- 提取特定AssesmentID的所有答案(分页)。
- 计算年龄键的特定AssessmentID的所有答案的平均值。
- 提取“年龄”键值大于25且关键“城市”等于“纽约”的所有响应
请注意，对于查询2，年龄值将存储在列AnswerValueDecimal中，在该列中，我将存储所有数字值。
我想知道列存储索引是否会提高这种结构中的性能？
请注意，我知道ElasticSearch实例将对我有很大帮助，但对于预算问题，我们在现阶段无法实现。
有关数据
的更多细节
对评估中的问题的答案通常是从有限的选择中选择的。例如：
- 你结婚了吗？> 是的，不
- 建筑类型？> 混凝土，骨架，永古板，铁皮，其他
- 城市面积？> 北，南，东，西，中
查询可以由最终用户构建，这取决于他们从特定评估中需要的信息。举上面的例子，他们想知道在城市的北部和西部有多少个永恒的建筑，最后算一算。
但举个例子，还有一个问题“你多大了？”，他们想要过滤之前的查询，知道在北部和西部地区有多少座永恒建筑，那里有一个年龄低于25年的人……
使用JSON字段存储数据
我尝试使用SQL Server 2016/2017中的新特性将JSON文档存储在NVARCHAR(MAX)字段中。它似乎运行得很好，但可能只在整个表加载到内存中之后。
下面是dbfiddle：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2ce135dc37d72f9db951bbb4e2708baa
回答 1
Database Administration用户
发布于 2019-02-07 16:04:57
还有什么比测试更好的方法呢？
泰瑞:前面的医生。当存在多个条件时，数据的结构方式会很尴尬，因为您必须检查不同的行以获得相同的响应。Columnstore在我的测试查询的自连接中使用批处理模式更好。
我期望您通过以不同的方式构造数据而不是通过查看存储选项来获得更多的好处。
这里有一个500万行的设置，最后还有一个示例查询，查找市中心的人的平均年龄，以获得一系列的响应(假设一项特定的调查)。杜德尔链接
DROP TABLE IF EXISTS dbo.AnswersRow
CREATE TABLE AnswersRow (
ResponseID INT NOT NULL,
QuestionID INT NOT NULL,
AnswerValueText VARCHAR(20) NULL,
AnswerValueDecimal DECIMAL(15,2) NULL,
CONSTRAINT PK_R_Q PRIMARY KEY CLUSTERED (ResponseID,QuestionID)
)
INSERT dbo.AnswersRow
SELECT TOP 5000000
FLOOR((-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))/5+1) AS ResponseID,
(-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))%5+1 AS QuestionID,
NULL AS AnswerValueText,
NULL AS AnswerValueDecimal
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b
--5 questions
--1) Married 0 or 1
--2) BuildingType
--3) CityArea
--4) Age
--5) Income
--randomize answers
UPDATE ar
SET AnswerValueText = CASE
WHEN QuestionID = 2 THEN (CASE x2.seed
WHEN 0 THEN 'Concrete'
WHEN 1 THEN 'Skeleton'
WHEN 2 THEN 'Eternite Sheets'
WHEN 3 THEN 'Iron Sheets'
WHEN 4 THEN 'Other'
END)
WHEN QuestionID = 3 THEN (CASE x3.seed
WHEN 0 THEN 'North'
WHEN 1 THEN 'South'
WHEN 2 THEN 'East'
WHEN 3 THEN 'West'
WHEN 4 THEN 'Center'
END)
ELSE AnswerValueText END,
ar.AnswerValueDecimal = CASE
WHEN QuestionID = 1 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*2))
WHEN QuestionID = 4 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*60+17))
WHEN QuestionID = 5 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*200000))
ELSE AnswerValueDecimal END
FROM dbo.AnswersRow ar
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x2
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x3
SELECT *
INTO dbo.AnswersCol
FROM dbo.AnswersRow
CREATE CLUSTERED COLUMNSTORE INDEX CX_Answers ON dbo.AnswersCol
CREATE NONCLUSTERED INDEX IX_Answer_text ON dbo.AnswersRow(AnswerValueText)
CREATE NONCLUSTERED INDEX IX_Answer_decimal ON dbo.AnswersRow(AnswerValueDecimal)
CREATE NONCLUSTERED INDEX IX_Answer_question ON dbo.AnswersRow(QuestionID) INCLUDE(AnswerValueText,AnswerValueDecimal)
--average age of particular survey, city center
SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersRow ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
FROM dbo.AnswersRow ar2
WHERE ar1.ResponseID = ar2.ResponseID
AND ar2.QuestionID = 3
AND ar2.AnswerValueText = 'Center'
)
SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersCol ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
FROM dbo.AnswersCol ar2
WHERE ar1.ResponseID = ar2.ResponseID
AND ar2.QuestionID = 3
AND ar2.AnswerValueText = 'Center'
)https://dba.stackexchange.com/questions/229119
复制相似问题
腾讯云开发者
