[Consulta Lenta] Como faço para melhorar minha consulta com bases remotas e subconsulta?

Question

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!" 改 善

Answer 1

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

Answer 2

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

Answer 3

Olá,

O que seria o CTE ?

Abs

---

K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

Answer 4

Bom dia,

No link abaixo tem um explicação.

http://technet.microsoft.com/pt-br/library/ms190766(v=sql.105).aspx

---

Atenciosamente, Ruberlei. www.t-sql.com.br

Answer 5

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!" 改 善

Answer 6

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

Answer 7

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!" 改 善

Answer 8

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

Answer 9

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

Answer 10

Vou fazer um teste aqui, mas preciso do group by e having para a minha regra de negócio.

Abs

---

K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

Answer 11

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!" 改 善

Answer 12

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

Answer 13

Gapimex,

Mesmo invertendo os CTE's, o resultado foi o mesmo com tanta demora que tive até que parar com a consulta.

Abs

---

K2rto'4 - Analista de Sistemas "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

Answer 14

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!" 改 善

Answer 15

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!" 改 善