Alternativa para select dinamico

[robizzotto]

Pessoal,

Tenho um site onde os estudantes resolvem questões (de vestibular e concursos). Tem uma tela onde o estudante pode ver as estatísticas de desempenho dele, segue um exemplo abaixo:

O meu problema é quanto mais questões o aluno resolve mais demorado fica para montar essa tabela, é muito calculo(selects). O que eu poderia fazer pra mostrar essas informações de um modo que fique mais rápido?

[Denis Courcy]

...

O meu problema é quanto mais questões o aluno resolve mais demorado fica para montar essa tabela, é muito calculo(selects). O que eu poderia fazer pra mostrar essas informações de um modo que fique mais rápido?

Leia sobre pivot table, otimização de consultas, modelos de dados star e snow flake.

[robizzotto]

Sobre o post acima, ainda estou lendo sobre pivot table e modelos de dados star e snow flake, mas não sei se daria para aplicar no meu caso.

Pensei no seguinte:

Segue abaixo algumas tabelas que estou utilizando para montar a tabela:(fora essas tem mais 2 para baixo)

Peguei o select abaixo como exemplo, ele lista todas as Organizadoras(bancas). Todos os select necessários para se montar a "planilha" acima fazem JOINs até chegar na ultima tabela(resposta_questao). resposta_questao registra a resposta de cada usuário para cada questão. Tem alguns selects que fazem mais dois Joins para baixo.

SELECT bancas.id_banca AS idBanca,

bancas.nome AS nomeBanca

FROM bancas

INNER JOIN

concursos AS concursos

ON concursos.id_banca = bancas.id_banca

INNER JOIN

provas AS provas

ON provas.id_concurso = concursos.id_concurso

INNER JOIN

provas_questoes AS provas_questoes

ON provas_questoes.id_prova = provas.id_prova

INNER JOIN

questoes

ON questoes.id_questao = provas_questoes.id_questao

INNER JOIN

resposta_questao AS resposta_questao

ON resposta_questao.id_questao = questoes.id_questao

WHERE resposta_questao.id_usuario = 576

GROUP BY bancas.nome

ORDER BY bancas.nome;

Será que não seria viavel e traria um melhor desempenho colocar dentro da tabela "resposta_questao", os ids(FKs) de todas as tabelas acima dela? Ai o select acima poderia ser subistituido por algo assim:

SELECT bancas.id_banca AS idBanca,

bancas.nome AS nomeBanca

FROM bancas

WHERE resposta_questao.id_banca = bancas.id_banca

AND resposta_questao.id_usuario = 576

GROUP BY bancas.nome

ORDER BY bancas.nome;

O que acha?

[Denis Courcy]

O modelo que você apresentou é um modelo relacional, próprio para o dia a dia de operações de input/output. Se você deseja, para efeito de performance, manter uma tabela desnormalizada, não há problema, desde que você documente.

Os modelos Star e snow flake, são modelos dimensionais, próprios para estatísticas e Informações gerenciais.

[robizzotto]

Bom, depois de ler muita coisa e fazer alguns testes cheguei aqui.

Link para visualizar a imagem melhor

Populei a tabela resposta_questao com 60 mil registros. De acordo com a imagem, coloquei o id_banca diretamente na tabela resposta_questao.

Select antes da alteração:

SELECT bancas.id_banca AS idBanca,

bancas.nome AS nomeBanca

FROM bancas

INNER JOIN

concursos AS concursos

ON concursos.id_banca = bancas.id_banca

INNER JOIN

provas AS provas

ON provas.id_concurso = concursos.id_concurso

INNER JOIN

provas_questoes AS provas_questoes

ON provas_questoes.id_prova = provas.id_prova

INNER JOIN

questoes

ON questoes.id_questao = provas_questoes.id_questao

INNER JOIN

resposta_questao AS resposta_questao

ON resposta_questao.id_questao = questoes.id_questao

WHERE resposta_questao.id_usuario = 576

GROUP BY bancas.nome

ORDER BY bancas.nome;

Select depois da alteração:

SELECT bancas.id_banca AS idBanca,

bancas.nome AS nomeBanca

FROM bancas, resposta_questao

WHERE resposta_questao.id_banca = bancas.id_banca

AND resposta_questao.id_usuario = 576

GROUP BY bancas.nome

ORDER BY bancas.nome;

No primeiro select o mysql nem consegue executar, já no segundo demorou 1 segundo. Até ai parece estar tudo certo. Mas como farei com os casos abaixo, onde preciso evitar os Joins e consultar nas tabelas disciplina e assunto??

SELECT disciplinas.id_disciplina AS idDisciplina,

disciplinas.nome AS nomeDisciplina

FROM bancas

INNER JOIN

concursos AS concursos

ON concursos.id_banca = bancas.id_banca

INNER JOIN

provas AS provas

ON provas.id_concurso = concursos.id_concurso

INNER JOIN

provas_questoes AS provas_questoes

ON provas_questoes.id_prova = provas.id_prova

INNER JOIN

questoes

ON questoes.id_questao = provas_questoes.id_questao

INNER JOIN

resposta_questao AS resposta_questao

ON resposta_questao.id_questao = questoes.id_questao

INNER JOIN

questoes_disciplina AS questoes_disciplina

ON resposta_questao.id_questao = questoes_disciplina.id_questao

INNER JOIN

disciplinas

ON disciplinas.id_disciplina = questoes_disciplina.id_disciplina

WHERE bancas.id_banca = 58

AND resposta_questao.id_usuario = 576

GROUP BY disciplinas.nome

ORDER BY disciplinas.nome;

SELECT assuntos.id_assunto AS idAssunto,

assuntos.id_diciplina AS idDisciplina,

assuntos.nome AS nomeAssunto

FROM bancas

INNER JOIN

concursos AS concursos

ON concursos.id_banca = bancas.id_banca

INNER JOIN

provas AS provas

ON provas.id_concurso = concursos.id_concurso

INNER JOIN

provas_questoes AS provas_questoes

ON provas_questoes.id_prova = provas.id_prova

INNER JOIN

questoes

ON questoes.id_questao = provas_questoes.id_questao

INNER JOIN

resposta_questao AS resposta_questao

ON resposta_questao.id_questao = questoes.id_questao

INNER JOIN

questoes_assuntos AS questoes_assuntos

ON resposta_questao.id_questao = questoes_assuntos.id_questao

INNER JOIN

assuntos

ON assuntos.id_assunto = questoes_assuntos.id_assunto

WHERE bancas.id_banca = 58

AND resposta_questao.id_usuario = 576

AND assuntos.id_diciplina = 6

GROUP BY assuntos.nome

ORDER BY assuntos.nome;

Editado Fevereiro 27, 2013 por robizzotto