Best Way to Store Varchar(MAX)

Asked

Viewed 173 times

0

I’m working on an EAD project where I work.

There will be simulations with many questions and their respective answers. These questions and some answers are usually quite large and I am storing them as VARCHAR(MAX) in an SQL Server database.

The table of simulations_questions has the following structure:

Cod int
Cod_simulado int
Pergunta varchar(max)
Comentario_Resposta varchar(max)
Pontos tinyint

The answer table is the same structure, the difference is that instead of Cod_simulated it is Cod_question.

However, in this database that I am working on, it has other non-standard tables that already have more than 20 thousand questions and to load this table takes a long time (around 30 seconds) because the inserted text values are large.

For a while, they decided to create several tables of the type Curso_x_questions and Curso_x_answers where "X" is the code of the course in the table courses to optimize the loading time of the questions and their respective answers. It worked well, but this would be the best and most correct way possible?

I am in doubt in which alternative to use, because in the long term I do not know what action I should take to optimize the future querys that will be executed, except by indexes. As there are thousands of accesses when the simulated ones are released, it will cause a great load in the table when we seek the questions and answers which can generate a lot of slowness in the site.

  • You need to figure out why it takes 30 seconds. I think he started to have an attitude to solve a problem, then everything becomes tragic. I don’t know what to answer there. I don’t know if I can answer with just this information.

  • Got it. Bigown, what information would you think it would be ideal for me to add the question?

  • 1

    Your question is wide and the question does not make much sense because there is no more of a way to use the VarChar(Max). Try walking into trouble to identify a specific bottleneck that may be the SELECT, the lack of an index, a JOIN, etc. Do not place the blame in the text field before analyzing other factors because this may hide the real problem.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.