0

Below is the query that I am running. Execution plan is at the bottom. It takes 8 minutes to complete and shows 'Query cost related to batch : 84%' in query plan. I haves fixed some required non-cluster indexes but it saved hardly a minute in runtime.

Select distinct 
    ItemSolicitacao.IdItemSolicitacao As IdItemSolicitacao, 
    ItemSolicitacao.IdSolicitacao As IdSolicitacao, 
    ItemSolicitacao.IdItemAplicacao As IdItemAplicacao, 
    ItemSolicitacao.Mapeado As Mapeado, 
    ItemSolicitacao.IdFuncao As IdFuncao, 
    ItemSolicitacao.IdDepartamento As IdDepartamento, 
    ItemSolicitacao.IdDeptoGrupo As IdDeptoGrupo, 
    ItemSolicitacao.Removido As Removido, 
    ItemSolicitacao.AtividadeNome As AtividadeNome, 
    ItemSolicitacao.Acao As Acao, 
    Aplicacao.Nome As AplicacaoNome, 
    dbo.OwnersItemAplicacao(ItemSolicitacao.IdItemAplicacao, ItemSolicitacao.IdSolicitacao) As Owner, 
    case 
        when((select count(d.codigo) from ControleXerox..departamento as d 
              where d.deptoPai = Departamento.Codigo) != 0)  
           then 1 
           else 0 
    end as Grupo, 
    Departamento.Nome As DepartamentoNome, 
    DeptoOutro.Nome As DepartamentoGrupoNome, 
    Funcao.Nome As FuncaoNome, 
    TipoAplicacao.Nome As TipoAplicacaoNome, 
    TipoItemAplicacao.Nome As TipoItemAplicacaoNome, 
    ItemAplicacao.Nome As ItemAplicacaoNome 
From 
    ItemSolicitacao 
Inner Join 
    ItemAplicacao ON ItemSolicitacao.IdItemAplicacao = ItemAplicacao.IdItemAplicacao 
Inner Join 
    Aplicacao ON ItemAplicacao.IdAplicacao = Aplicacao.IdAplicacao 
Inner Join 
    TipoAplicacao ON TipoAplicacao.IdTipoAplicacao = Aplicacao.IdTipoAplicacao 
Inner Join 
    TipoItemAplicacao ON ItemAplicacao.IdTipoItemAplicacao = TipoItemAplicacao.IdTipoItemAplicacao 
left Join 
    ControleXerox..Departamento ON Departamento.Codigo = ItemSolicitacao.IdDepartamento 
left Join 
    ControleXerox..Departamento as DeptoOutro ON DeptoOutro.Codigo = ItemSolicitacao.IdDeptoGrupo 
left Join 
    ControleXerox..Funcao ON Funcao.Codigo = ItemSolicitacao.IdFuncao 
Inner Join 
    AprovadorItemAplicacao ON AprovadorItemAplicacao.IdItemAplicacao = ItemAplicacao.IdItemAplicacao 
Inner Join 
    Aprovador on AprovadorItemAplicacao.idAprovador = Aprovador.idAprovador 
              and AprovadorItemAplicacao.IdTipoAprovador = 1

https://www.brentozar.com/pastetheplan/?id=B1ND0biUQ

Please find the details of scalar function.

CREATE FUNCTION [dbo].[OwnersItemAplicacao] 
(
    @IdItemAplicacao INT
    ,@IdSolicitacao INT
)
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @saida VARCHAR(2000)

    SET @saida = ' '

    DECLARE @owner VARCHAR(200)

    DECLARE cur CURSOR
    FOR
    SELECT DISTINCT vc.Nome
    FROM controlexerox..grupoEstacao
    INNER JOIN controlexerox..view_colaboradores vc ON grupoEstacao.codEstacao = (
            SELECT max(e.codigo)
            FROM controlexerox..workflowItem wi
            INNER JOIN controlexerox..estacao e ON e.codWorkflowItem = wi.codigo
                AND wi.codigoItem = @IdSolicitacao
            INNER JOIN controlexerox..grupoEstacao ge ON ge.codEstacao = e.codigo
                AND ge.idItemAplicacao = @IdItemAplicacao
            )
        AND grupoEstacao.LoginS3Responsavel = vc.codigo
    ORDER BY vc.Nome;

    OPEN cur

    FETCH NEXT
    FROM cur
    INTO @owner

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @saida = @saida + CHAR(10) + @owner

        FETCH NEXT
        FROM cur
        INTO @owner
    END

    CLOSE cur

    DEALLOCATE cur

    RETURN substring(@saida, 3, len(@saida))
END
James Z
  • 12,209
  • 10
  • 24
  • 44
Andy
  • 75
  • 1
  • 10
  • 1
    Hi and welcome to SO. A picture of an execution plan is nearly worthless. So much of the detail is missing. A better plan is to use a site like this one. https://www.brentozar.com/pastetheplan/ I would also suggest some aliases and white space to make that wall of text legible. – Sean Lange Aug 22 '18 at 16:19
  • Thanks Sean, query plan is updated as suggested by you. – Andy Aug 22 '18 at 16:27
  • I see a scalar function in your column list. Those are notoriously bad for performance. I don't see any other low hanging performance fruit in here. – Sean Lange Aug 22 '18 at 16:27
  • 2
    I don't even need to look very deep in that plan. That function is killing you. Want to see how much? Comment out the column that uses that function. You need a complete rewrite on that thing. It should be an inline table valued function, not a cursor. – Sean Lange Aug 22 '18 at 16:30
  • Could you please point out the column name? How would I optimise this? – Andy Aug 22 '18 at 16:31
  • The column is Owner. I think your function is creating a delimited list of values? There are better ways than a cursor for this. And that function is a bit sketchy too....it is joining on a subquery. – Sean Lange Aug 22 '18 at 16:35
  • Yes you are right. what else may I try to fetch the same list of values? – Andy Aug 22 '18 at 16:36
  • You can use STUFF with FOR XML. But I would take a long look at that function and not join to a subquery. That means that subquery executes for every single row in the main query. – Sean Lange Aug 22 '18 at 16:38
  • 3
    Also, for real optimization the indexes need to be evaluated. Just saying "they are good" often means that they really are not very good. – Sean Lange Aug 22 '18 at 16:41
  • Thanks for checking Sean, After commenting the column i am getting the data in 40 seconds. I will try to replace this as suggested by you. – Andy Aug 22 '18 at 16:45
  • Please find the details of scalar valued function – Andy Aug 22 '18 at 16:52
  • I tried to add the function script in question but I think i messed it. – Andy Aug 22 '18 at 16:58
  • It isn't beautiful but I edited the formatting of your function so it less painful to decipher. – Sean Lange Aug 22 '18 at 18:45
  • Thanks for this. Do I need to make any change in this function? Any suggestion that you want to give? – Andy Aug 22 '18 at 18:53
  • 1
    I wouldn't change that function, I would trash it and start over. Give me a little bit and I will see if I can cobble something together. Will be challenging with no tables to work with but I think I can do it...or at least get really close. – Sean Lange Aug 22 '18 at 18:57
  • Thanks a lot Sean, I can wait for your suggestion. You may ask if you need any other details. – Andy Aug 22 '18 at 18:58
  • Does your function also contain a view (view_colaboradores)? That could also be a problem in an optimized query if it's complex. – James Z Aug 22 '18 at 19:08
  • @Andy Please accept Seans answer if it helps, he has obviously put a lot of effort into helping you out here. – MJH Aug 29 '18 at 08:52
  • Hi. I just accepted the answer, Thanks Sean! – Andy Sep 18 '18 at 16:00

1 Answers1

3

This code is 100% untested because I have nothing to work with. But something along these lines should let you completely eliminate that scalar function. Keep in mind that sql is a declarative language and works best with sets. Doing processing RBAR (row by agonizing row) is going to suck the life out of your server.

Give this code a shot and see if it gets you pointed the right way.

Select distinct 
    s.IdItemSolicitacao
    , s.IdSolicitacao
    , s.IdItemAplicacao
    , s.Mapeado
    , s.IdFuncao
    , s.IdDepartamento
    , s.IdDeptoGrupo
    , s.Removido
    , s.AtividadeNome
    , s.Acao
    , a.Nome As AplicacaoNome
    --, dbo.OwnersItemAplicacao(s.IdItemAplicacao, s.IdSolicitacao) As Owner
    , Owner = x.NameList
    , case when (select count(d.codigo) from ControleXerox..departamento as dept where dept.deptoPai = d.Codigo) != 0 then 1 else 0 end as Grupo,
    , d.Nome As DepartamentoNome
    , DeptoOutro.Nome As DepartamentoGrupoNome
    , f.Nome As FuncaoNome
    , Tipoa.Nome As TipoAplicacaoNome
    , Tipoia.Nome As TipoItemAplicacaoNome
    , ia.Nome As ItemAplicacaoNome 
From ItemSolicitacao s
Inner Join ItemAplicacao ia ON s.IdItemAplicacao = ia.IdItemAplicacao 
Inner Join Aplicacao a ON ia.IdAplicacao = a.IdAplicacao 
Inner Join TipoAplicacao Tipoa ON Tipoa.IdTipoAplicacao = a.IdTipoAplicacao 
Inner Join TipoItemAplicacao Tipoia ON ia.IdTipoItemAplicacao = Tipoia.IdTipoItemAplicacao 
left Join ControleXerox..Departamento d ON d.Codigo = s.IdDepartamento 
left Join ControleXerox..Departamento as DeptoOutro ON DeptoOutro.Codigo = s.IdDeptoGrupo 
left Join ControleXerox..Funcao f ON f.Codigo = s.IdFuncao 
Inner Join AprovadorItemAplicacao Aprovadoria ON Aprovadoria.IdItemAplicacao = ia.IdItemAplicacao 
Inner Join Aprovador ON Aprovadoria.idAprovador = Aprovador.idAprovador 
    and Aprovadoria.IdTipoAprovador = 1
outer apply
(
    select NameList = STUFF((select char(10) + vc.Nome
                FROM controlexerox..grupoEstacao ge
                INNER JOIN controlexerox..view_colaboradores vc ON ge.codEstacao = 
                        (
                            SELECT max(e.codigo)
                            FROM controlexerox..workflowItem wi
                            INNER JOIN controlexerox..estacao e ON e.codWorkflowItem = wi.codigo
                                        AND wi.codigoItem = s.IdSolicitacao
                            INNER JOIN controlexerox..grupoEstacao ge ON ge.codEstacao = e.codigo
                                    AND ge.idItemAplicacao = s.IdItemAplicacao
                        )
                    AND grupoEstacao.LoginS3Responsavel = vc.codigo
                group by vc.Nome
                ORDER BY vc.Nome
                FOR XML PATH('')), 1, 1, '')
) x
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Thanks for the great effort. I am getting one error into this. 'the multi part identifier could not be bound' this error is coming on below line. AND grupoEstacao.LoginS3Responsavel = vc.codigo – Andy Aug 22 '18 at 19:38
  • Basic debugging...change that to ge.Login etc...I am a bit nervous that if you can't find that then you are not understanding this code. – Sean Lange Aug 22 '18 at 19:45
  • Thanks Sean, I did that. I got one more error after that but I am not able to resolve it. Msg 8120, Level 16, State 1, Line 1 Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Do I need to group by all the selected columns? – Andy Aug 22 '18 at 22:14
  • Please ignore my last comment(debugged). Your code is 100 % verified now. I am getting the exactly same output in 2 minutes and that saves 6 minutes compared to earlier code. That's more than enough for me and I am glad to deploy the same on Prod server. Thanks again for all your efforts to understand my query and resolve it. We can mark this question as solved. – Andy Aug 23 '18 at 05:23
  • Cool glad to hear you got it working. If this solves your question you should consider marking as the accepted answer. – Sean Lange Aug 23 '18 at 13:44
  • I have just accepted the answer. Thanks again, Sean! – Andy Sep 18 '18 at 15:58