I'm working on an EAD project where I work.
They will be simulated with many questions and their answers. These questions and some answers are usually quite large and I'm storing them as VARCHAR (MAX) on a SQL Server database.
The question-simulated table has the following structure:
Cod int
Cod_simulado int
Pergunta varchar(max)
Comentario_Resposta varchar(max)
Pontos tinyint
The response table is the same structure, the difference is that instead of Cod_simple is Cod_Question.
However, in this database I'm working on, it has other non-standard tables that already have more than 20,000 questions, and loading this table takes a long time (around 30 seconds) because the text values entered are large.
For a while, they decided to create several tables of type Course_X_Questions and Course_X_Responses where "X" is the course code in the table courses to optimize the loading time of the questions and their respective answers. It worked fine, but would this be the best and right way possible?
I am in doubt as to which alternative to use, because in the long run I do not know what action I should take to optimize the future queries that will be executed, except by indexes. As there are thousands of accesses when the simulated ones are released, it will be a big load on the table when we get the questions and answers, which can cause a lot of slowness on the site.