Pergunta conceitual: As consultas individuais são mais rápidas do que as junções ou: Devo tentar espremer todas as informações que desejo no lado do cliente em uma instrução SELECT ou apenas usar quantas parecer conveniente?
TL;DR : Se minha consulta associada demorar mais do que a execução de consultas individuais, isso é minha culpa ou isso é esperado?
Em primeiro lugar, não sou muito experiente em banco de dados, então pode ser apenas eu, mas notei que quando tenho que obter informações de várias tabelas, é "muitas vezes" mais rápido obter essas informações por meio de várias consultas em tabelas individuais (talvez contendo uma junção interna simples) e remendar os dados no lado do cliente para tentar escrever uma consulta unida (complexa) onde posso obter todos os dados em uma consulta.
Eu tentei colocar um exemplo extremamente simples juntos:
Configuração do esquema :
CREATE TABLE MASTER
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);
CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);
INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');
CREATE SEQUENCE SEQ_DATA_ID;
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);
Consulta A :
select NAME from MASTER
where ID = 1
| NAME |
--------
| One |
Pergunta B :
select ID, VALUE from DATA
where MASTER_ID = 1
| ID | VALUE |
--------------
| 1 | 1.3 |
| 2 | 1.5 |
| 3 | 1.7 |
Consulta C :
select M.NAME, D.ID, D.VALUE
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1
| NAME | ID | VALUE |
---------------------
| One | 1 | 1.3 |
| One | 2 | 1.5 |
| One | 3 | 1.7 |
Claro, não medi nenhum desempenho com estes, mas pode-se observar:
- A consulta A+B retorna a mesma quantidade de informações utilizáveis que a consulta C.
- A+B tem que retornar 1+2x3==7 "Células de Dados" para o cliente
- C tem que retornar 3x3==9 "Células de Dados" para o cliente, porque com a junção eu naturalmente incluo alguma redundância no conjunto de resultados.
Generalizando a partir disso (por mais improvável que seja):
Uma consulta combinada sempre deve retornar mais dados do que as consultas individuais que recebem a mesma quantidade de informações. Como o banco de dados precisa juntar os dados, para grandes conjuntos de dados , pode-se supor que o banco de dados precisa fazer mais trabalho em uma única consulta unida do que nas consultas individuais, pois (pelo menos) precisa retornar mais dados ao cliente.
A partir disso, quando observo que dividir uma consulta do lado do cliente em várias consultas produz um desempenho melhor, esse é apenas o caminho a seguir ou significaria que estraguei a consulta unida?
Em qualquer cenário de desempenho, você precisa testar e medir as soluções para ver qual é mais rápida .
Dito isso, é quase sempre o caso de um conjunto de resultados combinado de um banco de dados ajustado adequadamente ser mais rápido e dimensionar melhor do que retornar as linhas de origem ao cliente e depois juntá-las lá. Em particular, se os conjuntos de entrada forem grandes e o conjunto de resultados for pequeno -- pense na seguinte consulta no contexto de ambas as estratégias: junte duas tabelas com 5 GB cada, com um conjunto de resultados de 100 linhas. Isso é um extremo, mas você entende meu ponto.
É altamente provável que o esquema ou os índices do banco de dados possam ser aprimorados para atender melhor às consultas que você está lançando.
Normalmente este não é o caso. Na maioria das vezes, mesmo que os conjuntos de entrada sejam grandes, o conjunto de resultados será muito menor que a soma das entradas.
Dependendo do aplicativo, conjuntos de resultados de consulta muito grandes sendo retornados ao cliente são uma bandeira vermelha imediata: o que o cliente está fazendo com um conjunto tão grande de dados que não pode ser feito mais perto do banco de dados? A exibição de 1.000.000 de linhas para um usuário é altamente suspeita, para dizer o mínimo. A largura de banda da rede também é um recurso finito.
Não necessariamente. Se os dados estiverem indexados corretamente, é mais provável que a operação de junção seja feita com mais eficiência no banco de dados sem a necessidade de varrer uma grande quantidade de dados. Além disso, os mecanismos de banco de dados relacionais são especialmente otimizados em um nível baixo para junção ; pilhas de clientes não são.
Como você disse que é inexperiente quando se trata de bancos de dados, sugiro aprender mais sobre design de banco de dados e ajuste de desempenho. Tenho certeza que é aí que está o problema aqui. Consultas SQL escritas de forma ineficiente também são possíveis, mas com um esquema simples que é menos provável de ser um problema.
Agora, isso não quer dizer que não existam outras maneiras de melhorar o desempenho. Há cenários em que você pode optar por varrer um conjunto de dados médio a grande e devolvê-lo ao cliente se a intenção for usar algum tipo de mecanismo de armazenamento em cache. O armazenamento em cache pode ser ótimo, mas introduz complexidade em seu design. O armazenamento em cache pode nem ser apropriado para seu aplicativo.
Uma coisa que não foi mencionada em nenhum lugar é manter a consistência nos dados retornados do banco de dados. Se forem usadas consultas separadas, é mais provável (devido a muitos fatores) que dados inconsistentes sejam retornados, a menos que uma forma de isolamento de instantâneo seja usada para cada conjunto de consultas.
Você monta um bom código de exemplo. Você olhou para o tempo no SQL Fiddle? Mesmo alguns breves testes de desempenho não científicos mostrarão que a consulta três em sua demonstração leva aproximadamente a mesma quantidade de tempo para ser executada como a consulta um ou dois separadamente. Um e dois combinados levam cerca de duas vezes mais do que três e isso é antes que qualquer junção do lado do cliente seja executada.
À medida que você aumenta os dados, a velocidade da consulta um e dois divergiria, mas a junção do banco de dados ainda seria mais rápida.
Você também deve considerar o que aconteceria se a junção interna estivesse eliminando dados.
O otimizador de consulta também deve ser considerado. Seu papel é pegar seu SQL declarativo e traduzi-lo em etapas procedurais. Para encontrar a combinação mais eficiente de etapas procedurais, ele examinará combinações de uso de índice, classificações, armazenamento em cache de conjuntos de resultados intermediários e todo tipo de outras coisas também. O número de permutações pode ficar extremamente grande, mesmo com o que parecem consultas bastante simples.
Grande parte do cálculo feito para encontrar o melhor plano é orientado pela distribuição dos dados dentro das tabelas. Essas distribuições são amostradas e armazenadas como objetos de estatísticas. Se estiverem errados, levam o otimizador a fazer escolhas ruins. Escolhas ruins no início do plano levam a escolhas ainda piores mais tarde, em um efeito de bola de neve.
Não é incomum que uma consulta de tamanho médio que retorne quantidades modestas de dados leve alguns minutos para ser executada. Indexação correta e boas estatísticas reduzem isso a milissegundos.
Várias consultas é o caminho a percorrer. Se você lidar com cenários simples como esse - a sobrecarga de custo do otimizador de consulta é um fator. Com mais dados, entra a ineficiência da rede da junção (linhas redundantes). Somente com muito mais dados há eficiência.
No final, o que você experimenta é algo que muitos desenvolvedores veem. Os DBAs sempre dizem "não, faça uma junção", mas a realidade é: é mais rápido fazer várias seleções simples neste caso.