Dilemma of Generating tables dynamically to improve performance

best practicesdesign-pattern

Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An
interviewer goes to House 1 and interviews two members Member 1 and
Member 2. He asks questions like. What is this house address?, What is
your name? and What is your age?. The questions are created by
supervisors or survey experts

The way the data is stored for this application is as given below

The questions are saved in a table like below
+------------+--------------+----------+
| QUESTIONID | QUESTIONTEXT | SURVEYID |
+------------+--------------+----------+
|            |              |          |
| 1          | Name         | 311      |
|            |              |          |
| 2          | Gender       | 311      |
|            |              |          |
| 3          | Age          | 311      |
+------------+--------------+----------+
Results table (It has all the submissions)
+----------+----------+----------------+
| RESULTID | SURVEYID | SUBMITTED DATE |
+----------+----------+----------------+
|          |          |                |
| 90       | 311      | 11/12/2017     |
|          |          |                |
| 91       | 311      | 11/13/2017     |
+----------+----------+----------------+
Chunks table (where all the answers are stored)
+---------+----------+------------+----------+
| CHUNKID | RESULTID | QUESTIONID |  ANSWER  |
+---------+----------+------------+----------+
|         |          |            |          |
| 1001    | 90       | 1          | John Doe |
|         |          |            |          |
| 1002    | 90       | 2          | M        |
|         |          |            |          |
| 1003    | 90       | 3          | 18       |
|         |          |            |          |
| 1004    | 91       | 1          | Jane Doe |
|         |          |            |          |
| 1005    | 91       | 2          | F        |
|         |          |            |          |
| 1006    | 91       | 3          | 26       |
+---------+----------+------------+----------+

And the result after some Joins and Pivots will be like

| RESULTID | SUBMITTED DATE |   NAME   | GENDER | AGE |
+----------+----------------+----------+--------+-----+
|          |                |          |        |     |
| 90       | 11/12/2017     | John Doe | M      | 18  |
|          |                |          |        |     |
| 91       | 11/13/2017     | Jane Doe | F      | 26  |
+----------+----------------+----------+--------+-----+

Now the problem arises when we have really big surveys ( Some of them may go as far as 500 questions ). Normally the submissions exceed 20k. With this huge data, it takes forever to reach the final result.

So the solution I have in my mind is to generate a table dynamically when a survey is created.
like

Survey_311


+----------+----------------+----------+-----+-----+
| RESULTID | SUBMITTED DATE |   Q_1    | Q_2 | Q_3 |
+----------+----------------+----------+-----+-----+
|          |                |          |     |     |
| 90       | 11/12/2017     | John Doe | M   | 18  |
|          |                |          |     |     |
| 91       | 11/13/2017     | Jane Doe | F   | 26  |
+----------+----------------+----------+-----+-----+

And to insert rows each time an answer is submitted. But as I am not really a DBA and do not have much experience with Databases. I would like to know if this approach is a bad practice in this scenario, Any solutions other than paging that will improve the performance is also welcomed

Best Answer

Hope you have store information about House 1 and member detail somewhere.

Moreover purpose of Result Table is not clear to me.

Normally when you have slow running query then we take following steps :

i) Denormalize database : So that it reduce table join.At the same time don't disturb normalize table.So I will put SubmitDate in Chunks table

ii) Optimize query :It depend upon your query.what need to be optimize ?

iii)Introducing proper indexing

iv) Paging : As you said.

v) Partition Table

These are few step from the the best practices list and follow database design pattern.

Disadvantage of dynamic table.

i) I am not in favor of creating dynamic table like "Survey_311" because it sound like any design pattern.

ii) There will be several other kind of reports where you will at disadvantage like consolidating them will be problem.

iii) I see lot of dynamic Sql because of dynamic table.

iv) Database will be heavier very soonAlso there will be lot of indexes.

v) Maintainability : From both DBA and developer point of view.