Usuário com melhor resposta
[Consulta Lenta] Como faço para melhorar minha consulta com bases remotas e subconsulta?

Pergunta
-
Olá,
Esto fazendo uma query em um servidor que, nesta query, precisa ir até outro servidor e fazer uma consulta.
Na minha consulta tenho algo do tipo:
select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' and CPF in ( select substring(CPF, 2, 11) from Funcionarios where Data = '2014-11-03' group by CPF having count(*) = 1 ) group by CPF having count(*) > 1
A subconsulta retorna uns 500 registros. E a consulta principal atual em cima destes 500 registros para executar sua condição.
Se faço desta forma o sistema sql demoraaaaaaa muitoooooo para fazer a consulta. Na verdade nem consegui terminar de executar a consulta assim.
Agora se eu pego os CPF's retornados e faço a minha query da forma abaixo e sem a subconsulta, a query é executada super rápido em coisa de 5 segundos.
select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' and CPF in ( '12345678912', '123345678915', '...', '....' ) group by CPF having count(*) > 1
O que eu poderia fazer para melhorar a consulta com a subconsulta?
Talvez trocar o servidor principal que executa a query?
Obrigado
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
- Editado TI DEV quinta-feira, 6 de novembro de 2014 13:54
Respostas
-
Bom dia,
Acredito que já tenha mais não custa lembrar, as tabelas possuem índices.
Primeiramente criaria índice na sua tabela remota com os campos CPF e Dia.
Também criaria um índice na tabela funcionários com campo Data.
Onde tiver campo de data pode criar um índice filtrado que ajudaria.
---------------------------------------------------------------------------------------------------------------------------
Poderia criar uma CTE com seu subselect e utilizar a mesma no seu in, nesse caso, não sei se iria melhorar o desempenho, mais pode testar.
Seria algo assim:
;WITH SEUNOME AS ( select substring(CPF, 2, 11) AS CPF from Funcionarios where Data = '2014-11-03' group by CPF having count(*) = 1 ) select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' and CPF in ( SELECT CPF FROM SEUNOME s ) group by CPF having count(*) > 1
Atenciosamente, Ruberlei. www.t-sql.com.br
- Sugerido como Resposta Ruberlei quinta-feira, 6 de novembro de 2014 14:12
- Marcado como Resposta Ricardo Barbosa Cortes quinta-feira, 6 de novembro de 2014 18:17
- Não Marcado como Resposta Ricardo Barbosa Cortes quinta-feira, 6 de novembro de 2014 18:17
- Marcado como Resposta TI DEV sexta-feira, 7 de novembro de 2014 11:42
-
Experimente fazer outro teste:
with CTE_Funcionarios as ( select substring(CPF, 2, 11) as CPF from Funcionarios where Data = '2014-11-03' group by substring(CPF, 2, 11) having count(*) = 1 ), CTE_Acessos as ( select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' group by CPF having count(*) > 1 ) select a.CPF from CTE_Acessos as a inner join CTE_Funcionarios as f on f.CPF = a.CPF
Sobre a CTE, seria por ai mesmo.
Espero que ajude.
Assinatura: http://www.imoveisemexposicao.com.br
- Marcado como Resposta TI DEV quinta-feira, 6 de novembro de 2014 19:43
Todas as Respostas
-
Bom dia,
Experimente fazer um teste da seguinte forma para ver como fica:
with CTE_Funcionarios as ( select substring(CPF, 2, 11) as CPF from Funcionarios where Data = '2014-11-03' group by substring(CPF, 2, 11) having count(*) = 1 ) select a.CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] as a inner join CTE_Funcionarios as c on c.CPF = a.CPF where a.Dia >= '2014-11-03 00:00:00.000' and a.Dia <= '2014-11-03 23:59:59.999' group by a.CPF having count(*) > 1
Espero que ajude.
Assinatura: http://www.imoveisemexposicao.com.br
-
Bom dia,
Acredito que já tenha mais não custa lembrar, as tabelas possuem índices.
Primeiramente criaria índice na sua tabela remota com os campos CPF e Dia.
Também criaria um índice na tabela funcionários com campo Data.
Onde tiver campo de data pode criar um índice filtrado que ajudaria.
---------------------------------------------------------------------------------------------------------------------------
Poderia criar uma CTE com seu subselect e utilizar a mesma no seu in, nesse caso, não sei se iria melhorar o desempenho, mais pode testar.
Seria algo assim:
;WITH SEUNOME AS ( select substring(CPF, 2, 11) AS CPF from Funcionarios where Data = '2014-11-03' group by CPF having count(*) = 1 ) select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' and CPF in ( SELECT CPF FROM SEUNOME s ) group by CPF having count(*) > 1
Atenciosamente, Ruberlei. www.t-sql.com.br
- Sugerido como Resposta Ruberlei quinta-feira, 6 de novembro de 2014 14:12
- Marcado como Resposta Ricardo Barbosa Cortes quinta-feira, 6 de novembro de 2014 18:17
- Não Marcado como Resposta Ricardo Barbosa Cortes quinta-feira, 6 de novembro de 2014 18:17
- Marcado como Resposta TI DEV sexta-feira, 7 de novembro de 2014 11:42
-
-
-
Olá,
Obrigado pelas respostas.
Tentando aqui as soluções do gapimex e Ruberlei, informo que a consulta ainda continua muito lenta, sem ao menos terminar.
As tabelas aqui possuem MUITOSSSSSS registros.
Olhando para a solução com "CTE", seria algo como uma tabela temporária, certo?
Obrigado galera.
Este forum de SQL é muito bom.
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
- Editado TI DEV quinta-feira, 6 de novembro de 2014 16:45
-
Experimente fazer outro teste:
with CTE_Funcionarios as ( select substring(CPF, 2, 11) as CPF from Funcionarios where Data = '2014-11-03' group by substring(CPF, 2, 11) having count(*) = 1 ), CTE_Acessos as ( select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' group by CPF having count(*) > 1 ) select a.CPF from CTE_Acessos as a inner join CTE_Funcionarios as f on f.CPF = a.CPF
Sobre a CTE, seria por ai mesmo.
Espero que ajude.
Assinatura: http://www.imoveisemexposicao.com.br
- Marcado como Resposta TI DEV quinta-feira, 6 de novembro de 2014 19:43
-
Gapimex,
Muito boa a sua ideia de primeiro coletar os dados e depois relacionar as informações.
Infelizmente a consulta continua demorando e nem terminou. Pedi para cancelar lá pelos 12 minutos de execução.
Imagino que possa ser por termos muitos dados, não sei.
Segundo o seu exemplo temos dois grupos de dados.
No primeiro CTE_Funcionarios temos um retorno de 477 registros, e no segundo CTE_Acessos temos um retorno de 4323 registros.
Na minha opinião estes são valores baixos para afetar a performance do sql, sei lá.
Devo agora tentar criar mais filtros para melhorar a consulta.
Obrigado
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
- Editado TI DEV quinta-feira, 6 de novembro de 2014 17:59
-
Experimente fazer um teste invertendo as CTEs:
select f.CPF from CTE_Funcionarios as f inner join CTE_Acessos as a on a.CPF = f.CPF
E um outro teste utilizando o Exists:
select f.CPF from CTE_Funcionarios as f where exists (select 1 from CTE_Acessos as a where a.CPF = f.CPF)
Espero que ajude.
Assinatura: http://www.imoveisemexposicao.com.br
-
Se você retirar o group by e o having da sua consulta externa ... a sua consulta retorna em quanto tempo ?
Select CPF from [ServerRemoto].[TabelaRemota].[dbo].[Acessos] where Dia >= '2014-11-03 00:00:00.000' and Dia <= '2014-11-03 23:59:59.999' and CPF in ( select substring(CPF, 2, 11) from Funcionarios where Data = '2014-11-03' group by CPF having count(*) = 1 )
Tulio Rosa | http://tuliorosa.com.br | Se resolveu seu problema, marque como resposta ou vote
-
-
Tulio Rosa,
Tirei aqui o group by e o having e a consulta demorou muito também que até tive que parara antes do fim.
Tirando o group by e having tenho TODOS os dados para o dia mencionado.
Talvez o problema ocorra por estar envolvendo uma consulta remota, sei la.
Abs
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
-
Tulio Rosa,
Tirei aqui o group by e o having e a consulta demorou muito também que até tive que parara antes do fim.
Tirando o group by e having tenho TODOS os dados para o dia mencionado.
Talvez o problema ocorra por estar envolvendo uma consulta remota, sei la.
Abs
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
Você tem essas duas tabelas no servidor remoto ? (Acessos e Funcionarios)
Se tiver como executar sua consulta localmente sem usar o Linked Server e ficar rápido, uma solução seria criar uma SP no servidor remoto e chama-la do outro servidor
Tulio Rosa | http://tuliorosa.com.br | Se resolveu seu problema, marque como resposta ou vote
-
-
Tulio Rosa,
Tenho cada tabela em um servidor diferente.
O que poderia eu fazer é mudar quem acessa quem. Talvez assim melhore um pouco a performance.
Eu até tentei fazer isto, mas em um dos servidores não existe um Linked Server apontando para o outro.
Abs
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善
-
Pessoal, boas novas.
Consegui trocar o servidor que executa a consulta.
Desta forma a consulta foi SUPER rápida!!!!
Porque será hein que do servidor A para o B demora e do servidor B para o A é rápido?
Abs
K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善