Sql-server – SQL Server Database Design for Reporting

sql server

We are designing a reporting solution for survey results. Although datasets are reasonable in size (rarely more than 500.000 respondents and 50 questions), performance is obviously a major concern.

Due to the nature of the solution, most queries return aggregated values and no locks are needed.

Storing answers in a "normal" tabular format (i.e. a column for each question and a row for each respondent) works well in terms of performance, and allows us to query the data like so:

SELECT COUNT(*) FROM Answers WHERE Gender = 'M' and Age < 20

However, this design requires a new table for each survey as the questions (columns) differ, which is obviously not an ideal solution.

Therefore, we are considering a design where we store answer data in a table that would basically just hold a respondent ID, a question ID and an answer value, thereby "transposing" the data (i.e. there would be a row for each respondent/question combination in the Answers table).

In this design, we would have to use exists conditions (or joins) to filter our data, e.g:

SELECT COUNT(*) 
FROM Answers AS A1 
WHERE A1.QuestionID = 'Gender' AND A1.VALUE = 'M' AND EXISTS
( 
    SELECT * 
    FROM Answers AS A2 
    WHERE A2.RespondentID = A1.RespondentID AND A2.QuestionID = 'Age' AND A2.Value > 18
)

This would allow as to handle any survey without changing the database schema but we are concerned about what the impact might be on performance?

Or perhaps there is a better way to deal with this issue altogether?

Best Answer

We did some performance tests and found that - with scenarios that are typical for our application domain - it is 10-20 times faster to query data when stored in a regular "tabular" format.

Related Solutions

SQL Server – Stored Procedure to Return Dynamically Created Table Data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script

SQL Server – Reporting on Normalized Key-Value Pivot

Hello from 2 years later! I've lived and learned and realized that a key-value model is probably actually an ideal purpose for NoSQL. But our org/team didn't have access to a NoSQL Document DB, past or present.

The way that we have it set up makes it easy to create new "surveys", but difficult to report on. The best solution I can come up with, as efficiently as I can, was to create a view for each survey....

WITH
BestSingers AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS BestSinger
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Best Singer'
),
HottestSingers AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS HottestSinger
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Hottest Singer'
),
RyanSeacreastFans AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS IsRyanSeacrestFan
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Do you like Ryan Seacrest?'
)

SELECT
  SS.Id AS SurveySubmissionId,
  BestSingers.BestSinger,
  HottestSingers.HottestSinger,
  RyanSeacrestFans.IsRyanSeacrestFan
FROM SurveySubmissions SS
LEFT JOIN BestSingers ON BestSingers.SurveySubmissionId = SS.Id
LEFT JOIN HottestSingers ON HottestSingers.SurveySubmissionId = SS.Id
LEFT JOIN RyanSeacreastFans ON RyanSeacreastFans.SurveySubmissionId = SS.Id
WHERE SS.SurveyId = 1

But wait...

I got lucky that our "Question Keys" happen to be one-word (PascalCase) without any punctuation/spaces. So check out this dynamic SQL monster, that scrapes the question keys for a given survey and turns them into column headings.

DECLARE @surveyId int = 1;
DECLARE @surveyIdStr nvarchar(10) = CAST(@surveyId AS nvarchar(10));

DECLARE @dynamicCte nvarchar(max) = 'WITH ';

SET @dynamicCte = @dynamicCte + (SELECT STUFF(
  (SELECT 
        N', ' + 
        SIK.[Name] + 's AS ' +
        '
        (
          SELECT 
            SS.Id AS SurveySubmissionId, 
            SIV.JsonValue AS ' + SIK.[Name] + '
          FROM [SurveySubmissions] SS WITH(NOLOCK)
          INNER JOIN [SurveyInputValues] SIV ON SIV.SurveySubmissionId = SS.Id
          INNER JOIN [SurveyInputKeys] SIK ON SIV.SurveyInputKeyId = SIK.Id
          WHERE SIK.Name = ''' + SIK.[Name] + '''
          AND SIK.SurveyId = ' + @surveyIdStr + '
        )
        '
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicCte;


DECLARE @dynamicSelect nvarchar(max) = 
'SELECT TOP 100
    SS.Id AS SurveySubmissionId, ';

SET @dynamicSelect = @dynamicSelect + (SELECT STUFF(
  (SELECT 
        N', ' + 
        SIK.[Name] + 's.' + SIK.[Name]
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicSelect;

SET @dynamicSelect = @dynamicSelect + 
'
    FROM [SurveySubmissions] SS WITH(NOLOCK) 
    INNER JOIN [SurveyResults] SR ON SS.SurveyResultId = SR.Id
';


DECLARE @dynamicJoin nvarchar(max);

SET @dynamicJoin = (SELECT STUFF(
  (SELECT 
        N'  ' + 
        '
            LEFT JOIN ' + SIK.[Name] +'s ON ' + SIK.[Name] + 's.SurveySubmissionId = SS.Id
        '
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicJoin;


DECLARE @dynamicWhere nvarchar(max) = 
'
    WHERE SS.SurveyId = ' + @surveyIdStr + '
    AND SS.SubmittedDatetime > ''2017-02-24''
    ORDER BY SS.Id DESC
';

DECLARE @dynamicSql nvarchar(max) = @dynamicCte + @dynamicSelect + @dynamicJoin + @dynamicWhere;
--SELECT @dynamicSql;

EXEC SP_EXECUTESQL @dynamicSql;

Psuedo-output...

 SubmissionId  |   BestSinger    |    HottestSinger   |  IsRyanSeacrestFan
      1        |    "Adele"      |    "Katy Perry"    |     true
      2        | "Justin Bieber" |  "Free Form Text"  |     false
      3        |  "John Doe"     |     "Elvis"        |     true

Thoughts? Is this the best of SQL Server mousetraps?

Best Answer

Related Solutions

SQL Server – Stored Procedure to Return Dynamically Created Table Data

SQL Server – Reporting on Normalized Key-Value Pivot

But wait...

Related Question