Dilemma of Generating tables dynamically to improve performance 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
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.