Slow query that includes a WHERE clause with SUBSTRING 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 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.