none
Query lenta RRS feed

  • Pergunta

  • tenha a seguinte stored procedure:

     

    Code Snippet

    if @p_func = '1' or @p_func = 'L'
    begin
     select @strSQL = 'SELECT DISTINCT tr.cod_ocor, tr.num_ctrl_ocor, tr.des_area_ocor, tr.cod_area_ocor, tr.des_ocor
       ,tr.cod_moed_envo, tr.val_ocor_envo, tr.cod_moed_perd, tr.val_ocor_perd, tr.cod_moed_ganh
       ,tr.val_ocor_ganh, tr.des_acao, tr.cod_stat, tr.dat_cadm_ocor, tr.des_logi_soli, tr.dat_ocor
       ,tu.nom_comp, tr.dat_manu_soli
       ,nom_coor = t5.nom_comp, tr.des_logi_coor, tr.des_obsv_coor, tr.dat_manu_coor
       ,nom_supe = t4.nom_comp, tr.des_logi_supe, tr.des_obsv_supe, tr.dat_manu_supe
       ,nom_dire = t3.nom_comp, tr.des_logi_dire, tr.des_obsv_dire, tr.dat_manu_dire
       ,tr.cod_meti_1, tr.cod_meti_2, tr.cod_meti_3, tr.cod_meti_4, tr.cod_meti_5, tr.cod_risc
       ,tr.cod_tipo_1, tr.cod_tipo_2, tr.cod_tipo_3, tr.cod_tipo_4, tr.cod_tipo_5, tr.cod_tipo_6
       ,tr.cod_tipo_7, tr.des_outr_tipo, tr.val_qtde_tipo_1, tr.val_qtde_tipo_2
       ,des_logi_coor_cont = isnull(tc.des_logi_coor_cont, '''')
       ,des_nome_coor_cont = isnull(case when tc.des_logi_coor_cont <> '''' or tc.des_logi_coor_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_coor_cont = des_logi) else '''' end, '''')
       ,des_logi_supe_cont = isnull(tc.des_logi_supe_cont, '''')
       ,des_nome_supe_cont = isnull(case when tc.des_logi_supe_cont <> '''' or tc.des_logi_supe_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_supe_cont = des_logi) else '''' end, '''')
       ,des_logi_dire_cont = isnull(tc.des_logi_dire_cont, '''')
       ,des_nome_dire_cont = isnull(case when tc.des_logi_dire_cont <> '''' or tc.des_logi_dire_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_dire_cont = des_logi) else '''' end, '''')
       ,des_area = isnull(ta.des_area, '''')
       ,des_tipo = isnull(tt.des_tipo, '''')   

       FROM   tbl_rod_ocor  tr

       left join tbl_rod_ocor_user  td
       on  td.cod_ocor  = tr.cod_ocor

       left join tbl_rod_ocor_cont tc
       on  tc.cod_ocor  = tr.cod_ocor

       inner join tbl_intr_usua  tu
       on  tu.des_logi  = tr.des_logi_soli

       left join tbl_intr_usua  t5
       on  t5.des_logi  = tr.des_logi_coor

       left join tbl_intr_usua  t4
       on  t4.des_logi  = tr.des_logi_supe

       inner join tbl_intr_usua  t3
       on  t3.des_logi  = tr.des_logi_dire

       left join tbl_intr_area  ta
       on  tr.cod_area_resp = ta.cod_area

       left join tbl_rod_tipo  tt
       on  tr.cod_tipo  = tt.cod_tipo

       WHERE  tr.cod_stat   >= ' + @p_cod_stat_inil + '
       AND  tr.cod_stat    <= ' + @p_cod_stat_finl + char(13)

     if @p_tip_data = 'dt_regi'
     begin
      select @strSQL = @strSQL + 'AND tr.dat_cadm_ocor >= ''' + ltrim(rtrim(@p_dat_inil_conv)) + '''' + char(13)
      select @strSQL = @strSQL + 'AND tr.dat_cadm_ocor <= ''' + ltrim(rtrim(@p_dat_finl_conv)) + '''' + char(13)
     end

     if @p_tip_data = 'dt_ocor'
     begin
      select @strSQL = @strSQL + 'AND tr.dat_ocor >= ''' + ltrim(rtrim(@p_dat_inil_conv)) + ''''  + char(13)
      select @strSQL = @strSQL + 'AND tr.dat_ocor <= ''' + ltrim(rtrim(@p_dat_finl_conv)) + '''' + char(13)
     end

     if @p_des_auto = 'GERE'
     begin
      select @strSQL = @strSQL + 'AND (substring(tu.des_orga, 1, 3)    = ''' + @p_des_patr_2 + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_coor = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_supe = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_dire = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  td.des_logi_user = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_soli = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_coor_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_supe_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_dire_cont = ''' + @p_logi + ''')' + char(13)
     end

     if @p_des_auto = 'AREA'
     begin
      select @strSQL = @strSQL + 'AND (substring(tu.des_orga, 1, 5)    = ''' + @p_des_patr_3 + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_coor = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_supe = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_dire = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  td.des_logi_user = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_soli = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_coor_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_supe_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_dire_cont = ''' + @p_logi + ''')' + char(13)
     end

     if @p_des_auto = 'DEPT'
     begin
      select @strSQL = @strSQL + 'AND (tu.des_orga like ''%' + ltrim(rtrim(@p_des_orga_logi)) + '%''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_coor = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_supe = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_dire = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  td.des_logi_user = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_soli = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_coor_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_supe_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_dire_cont = ''' + @p_logi + ''')' + char(13)
     end

     if @p_des_auto = 'USUA'
     begin
      select @strSQL = @strSQL + 'AND (tr.des_logi_soli = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_coor = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_supe = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.des_logi_dire = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  td.des_logi_user = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_coor_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_supe_cont = ''' + @p_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tc.des_logi_dire_cont = ''' + @p_logi + ''')' + char(13)
     end

     if @p_cod_tipo <> ''
     begin
      select @strSQL = @strSQL + 'AND (tr.cod_tipo_1 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_2 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_3 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_4 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_5 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_6 =  ''' + @p_cod_tipo  + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_tipo_7 =  ''' + @p_cod_tipo  + ''')' + char(13)
     end

     if @p_cod_risc <> ''
     begin
      select @strSQL = @strSQL + 'AND tr.cod_risc = ''' + @p_cod_risc + '''' + char(13)
     end

     if @p_cod_meti <> ''
     begin
      select @strSQL = @strSQL + 'AND (tr.cod_meti_1 =  ''' + @p_cod_meti + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_2 =  ''' + @p_cod_meti + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_3 =  ''' + @p_cod_meti + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_4 =  ''' + @p_cod_meti + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_5 =  ''' + @p_cod_meti + ''')' + char(13)
     end

     if @p_cod_area <> ''
     begin
      select @strSQL = @strSQL + 'AND tr.des_area_ocor like ''%' + @p_cod_area + '%''' + char(13)
     end

     if @p_num_ctrl_ocor <> ''
     begin
      select @strSQL = @strSQL + 'AND tr.num_ctrl_ocor = ''' + @p_num_ctrl_ocor + '''' + char(13)
     end

     if @p_cod_meti_logi <> ''
     begin
      select @strSQL = @strSQL + 'UNION ALL' + char(13) + char(13)
      select @strSQL = @strSQL + 'SELECT DISTINCT tr.cod_ocor, tr.num_ctrl_ocor, tr.des_area_ocor, tr.cod_area_ocor, tr.des_ocor
          ,tr.cod_moed_envo, tr.val_ocor_envo, tr.cod_moed_perd, tr.val_ocor_perd, tr.cod_moed_ganh
          ,tr.val_ocor_ganh, tr.des_acao, tr.cod_stat, tr.dat_cadm_ocor, tr.des_logi_soli, tr.dat_ocor
          ,tu.nom_comp, tr.dat_manu_soli
          ,nom_coor = t5.nom_comp, tr.des_logi_coor, tr.des_obsv_coor, tr.dat_manu_coor
          ,nom_supe = t4.nom_comp, tr.des_logi_supe, tr.des_obsv_supe, tr.dat_manu_supe
          ,nom_dire = t3.nom_comp, tr.des_logi_dire, tr.des_obsv_dire, tr.dat_manu_dire
          ,tr.cod_meti_1, tr.cod_meti_2, tr.cod_meti_3, tr.cod_meti_4, tr.cod_meti_5, tr.cod_risc
          ,tr.cod_tipo_1, tr.cod_tipo_2, tr.cod_tipo_3, tr.cod_tipo_4, tr.cod_tipo_5, tr.cod_tipo_6
          ,tr.cod_tipo_7, tr.des_outr_tipo, tr.val_qtde_tipo_1, tr.val_qtde_tipo_2
          ,des_logi_coor_cont = isnull(tc.des_logi_coor_cont, '''')
          ,des_nome_coor_cont = isnull(case when tc.des_logi_coor_cont <> '''' or tc.des_logi_coor_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_coor_cont = des_logi) else '''' end, '''')
          ,des_logi_supe_cont = isnull(tc.des_logi_supe_cont, '''')
          ,des_nome_supe_cont = isnull(case when tc.des_logi_supe_cont <> '''' or tc.des_logi_supe_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_supe_cont = des_logi) else '''' end, '''')
          ,des_logi_dire_cont = isnull(tc.des_logi_dire_cont, '''')
          ,des_nome_dire_cont = isnull(case when tc.des_logi_dire_cont <> '''' or tc.des_logi_dire_cont is not null then (select nom_comp from tbl_intr_usua where tc.des_logi_dire_cont = des_logi) else '''' end, '''')
     
          FROM   tbl_rod_ocor  tr
       
          left join tbl_rod_ocor_user  td
          on  td.cod_ocor  = tr.cod_ocor
       
          left join tbl_rod_ocor_cont tc
          on  tc.cod_ocor  = tr.cod_ocor
       
          inner join tbl_intr_usua  tu
          on  tu.des_logi  = tr.des_logi_soli
       
          left join tbl_intr_usua  t5
          on  t5.des_logi  = tr.des_logi_coor

          left join tbl_intr_usua  t4
          on  t4.des_logi  = tr.des_logi_supe
       
          inner join tbl_intr_usua  t3
          on  t3.des_logi  = tr.des_logi_dire

         WHERE  tr.cod_stat   >= ' + @p_cod_stat_inil + '
         AND  tr.cod_stat    <= ' + @p_cod_stat_finl + char(13)
       
      select @strSQL = @strSQL + 'AND (tr.cod_meti_1 =  ''' + @p_cod_meti_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_2 =  ''' + @p_cod_meti_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_3 =  ''' + @p_cod_meti_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_4 =  ''' + @p_cod_meti_logi + '''' + char(13)
      select @strSQL = @strSQL + 'OR  tr.cod_meti_5 =  ''' + @p_cod_meti_logi + ''')' + char(13)

      if @p_tip_data = 'dt_regi'
      begin
       select @strSQL = @strSQL + 'AND tr.dat_cadm_ocor >= ''' + ltrim(rtrim(@p_dat_inil_conv)) + '''' + char(13)
       select @strSQL = @strSQL + 'AND tr.dat_cadm_ocor <= ''' + ltrim(rtrim(@p_dat_finl_conv)) + '''' + char(13)
      end
     
      if @p_tip_data = 'dt_ocor'
      begin
       select @strSQL = @strSQL + 'AND tr.dat_ocor >= ''' + ltrim(rtrim(@p_dat_inil_conv)) + ''''  + char(13)
       select @strSQL = @strSQL + 'AND tr.dat_ocor <= ''' + ltrim(rtrim(@p_dat_finl_conv)) + '''' + char(13)
      end

      if @p_cod_tipo <> ''
      begin
       select @strSQL = @strSQL + 'AND (tr.cod_tipo_1 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_2 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_3 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_4 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_5 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_6 =  ''' + @p_cod_tipo  + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_tipo_7 =  ''' + @p_cod_tipo  + ''')' + char(13)
      end
     
      if @p_cod_risc <> ''
      begin
       select @strSQL = @strSQL + 'AND tr.cod_risc = ''' + @p_cod_risc + '''' + char(13)
      end
     
      if @p_cod_meti <> ''
      begin
       select @strSQL = @strSQL + 'AND (tr.cod_meti_1 =  ''' + @p_cod_meti + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_meti_2 =  ''' + @p_cod_meti + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_meti_3 =  ''' + @p_cod_meti + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_meti_4 =  ''' + @p_cod_meti + '''' + char(13)
       select @strSQL = @strSQL + 'OR  tr.cod_meti_5 =  ''' + @p_cod_meti + ''')' + char(13)
      end
     
      if @p_cod_area <> ''
      begin
       select @strSQL = @strSQL + 'AND tr.des_area_ocor like ''%' + @p_cod_area + '%''' + char(13)
      end
     
      if @p_num_ctrl_ocor <> ''
      begin
       select @strSQL = @strSQL + 'AND tr.num_ctrl_ocor = ''' + @p_num_ctrl_ocor + '''' + char(13)
      end
     end

     select @strSQL = @strSQL + 'ORDER BY tr.num_ctrl_ocor desc' + char(13) + char(13)

     

    EXECUTE sp_executesql @strSQL
    end

     

     

    ela está demorando uns 10 segundos para retornar os dados; atualmente ele me retorno 2239 registros
    terça-feira, 4 de março de 2008 12:15

Todas as Respostas

  • Bom dia Fernando

     

    Basicamente é o seguinte, fica complicado analisar sua Query sem conhecer a estrutura das suas tabelas e do seu banco, um dos problemas principais no seu caso é o fato de você estar utilizando uma Query Dinâmica, sem dúvida você vai perder um pouco de performance. Acho que no seu caso o mais prático é você gerar o plano de execução da sua consulta e analisar os pontos que estão causando lentidão.

     

     

     

     

     

     

     

     

     

     

     

    Espero ter ajudado

    terça-feira, 4 de março de 2008 12:45
  • Bom dia,

     

    Considerando a quantidade de LIKEs e ORs que a procedure possui não é de se estranhar uma queda de desempenho (LIKE e OR são matadores de índices natos). Além do fato de ser dinâmica, existem muitas considerações a serem feitas (IFs). Minha sugestão é que você não crie uma "super procedure" para tudo. Divida essa procedure em procedures menores e no seu programa faça a avaliação para escolher a procedure correta.

     

    Se ainda achar que não é suficiente, use o Profiler com Database Tuning Advisor ou o Tuning Wizard (SQL 2000)

     

    [ ]s,

     

    Gustavo

    terça-feira, 4 de março de 2008 13:09
  • Pessoal, concordo plenamente.

    terça-feira, 4 de março de 2008 13:31