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

  • 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
    quinta-feira, 6 de novembro de 2014 13:46

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
    quinta-feira, 6 de novembro de 2014 14:05
  • 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
    quinta-feira, 6 de novembro de 2014 17:23

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

    quinta-feira, 6 de novembro de 2014 14:00
  • 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
    quinta-feira, 6 de novembro de 2014 14:05
  • Olá,

    O que seria o CTE ?

    Abs


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

    quinta-feira, 6 de novembro de 2014 14:07
  • 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

    • Sugerido como Resposta Ruberlei quinta-feira, 6 de novembro de 2014 14:12
    quinta-feira, 6 de novembro de 2014 14:10
  • 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
    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
    quinta-feira, 6 de novembro de 2014 17:23
  • 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
    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

    quinta-feira, 6 de novembro de 2014 18:11
  • 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

    quinta-feira, 6 de novembro de 2014 18:11
  • 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!" 改 善

    quinta-feira, 6 de novembro de 2014 18:36
  • 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!" 改 善

    quinta-feira, 6 de novembro de 2014 18:45
  • 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

    quinta-feira, 6 de novembro de 2014 18:58
  • 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!" 改 善

    quinta-feira, 6 de novembro de 2014 19:04
  • 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!" 改 善

    quinta-feira, 6 de novembro de 2014 19:06
  • 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!" 改 善

    sexta-feira, 7 de novembro de 2014 16:44