2

I have the following query in SQL Server 2016, as there is no code for the "enter code here" column, I had to do a search with SUBSTRING. But as expected it is slow, the query ends in 00:03:37.

with cte as
(
    -- De Incidente para Requisição
    select 
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço'
    union
    -- De Requisição para Incidente  
    select 
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente'
)
select * from cte

I thought about making a temporary table and inserting only the data I need, so this table would update itself through a job in SQL Server Agent. But I do not know if it is the best solution.

  • You can at least avoid the union and have two substring checks with OR (or try to compare only the part which is the same). Better is of course by normalizing this column or caching the substring result in extra column. – eckes Jan 29 '19 at 11:28
1

IMO, it is not prime candidate for computed columns instead it is important to know how the table get populated via UI or any Job .May be better solution lies here.

Another point is why the predicate are hard coded,can it change frequently ?

Another point is that @Erik pointed, UNION, eliminating UNION will boost your query.

with cte as
(
    -- De Incidente para Requisição
    select 
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço'

)


    -- De Requisição para Incidente  
    select 
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente'
        and not exists(select 1 from CTE 1 where hd_acompanhamento].somekeyCol = c.somekeyCol)
        union all
    select * from cte
4

You could create two computed columns for this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsServiceRequest AS 
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1 
    ELSE 0 END;

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsIncident AS 
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 1 
    ELSE 0 END;

And then index them:

CREATE INDEX IX_IsServiceRequest 
ON [dbo].[hd_acompanhamento] (IsServiceRequest);

CREATE INDEX IX_IsIncident 
ON [dbo].[hd_acompanhamento] (IsIncident);

Then you should be able to rewrite your query like this:

with cte as
(
    -- De Incidente para Requisição
    select 
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        IsIncident = 1
    union
    -- De Requisição para Incidente  
    select 
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from 
        [dbo].[hd_acompanhamento] 
    where 
        IsServiceRequest = 1
)
select * from cte

This will let your query seek to those specific rows, rather than scanning the whole table running the substring function.

You may also want to add the columns cdchamado and dtacompanhamento as included columns for the index to avoid a key lookup. I didn't include them by default, because I'm not sure of their data types. If they are big text values, it might be better to just leave them as key lookups.


Another option if you only want the overhead of one computed column / index, you could combine them like this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD ServiceRequestType AS 
CASE 
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1 
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 2
    ELSE 0 
END;

Then you could index just that one column, and use ServiceType = 1 and ServiceType = 2 in the two queries being union'd together. Hat tip to Erik Darling for that idea.

  • It may also be worth looking at union all instead of union, but that's hard to say without the plan. – Erik Darling Jan 28 '19 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.