VIEW – INDEX SQL SERVEr

Does anyone know another way to run the query below more efficiently? I’m using SQL Server 2014. I’m facing a problem if i create index on table or on view .

On table 2 (TB_FATURA_ITEM_TRANSACAO_HST) , has stored 50 million rows .

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW vItemFatura  (ID , ID_TRANSACAO , ID_FATURA ,ID_FATURA_ITEM ,ID_TIPO_SERVICO , ID_SERVICO  ,ID_TARIFA ,DATA_REGISTRO_TRANSACAO ,DATA_PAGAMENTO ,CODIGO_REFERENCIA , DESCRICAO ,MERCHANT_ID,ORDER_ID,IDENTIFICACAO_TITULO,CNPJ,NUMERO_AGENCIA,NUMERO_CONTA,QUANTIDADE_ITENS,VALOR_ITEM,VALOR_TRANSACAO,STATUS_TRANSACAO,DATA_INI_CONTABILIZACAO,DATA_FIM_CONTABILIZACAO,DATA_INI_RETROATIVO,DATA_FIM_RETROATIVO,ATIVO,DATA_INSERT,DATA_UPDATE,APP_INSERT,APP_UPDATE,VALOR_PEDIDO_EC)
AS  
SELECT 
      T1.ID
     ,T1.ID_TRANSACAO
     ,T1.ID_FATURA
     ,T1.ID_FATURA_ITEM
     ,T1.ID_TIPO_SERVICO
     ,T1.ID_SERVICO
     ,T1.ID_TARIFA
     ,T1.DATA_REGISTRO_TRANSACAO
     ,T1.DATA_PAGAMENTO
     ,T1.CODIGO_REFERENCIA
     ,T1.DESCRICAO
     ,T1.MERCHANT_ID
     ,T1.ORDER_ID
     ,T1.IDENTIFICACAO_TITULO
     ,T1.CNPJ
     ,T1.NUMERO_AGENCIA
     ,T1.NUMERO_CONTA
     ,T1.QUANTIDADE_ITENS
     ,T1.VALOR_ITEM
     ,T1.VALOR_TRANSACAO
     ,T1.STATUS_TRANSACAO
     ,T1.DATA_INI_CONTABILIZACAO
     ,T1.DATA_FIM_CONTABILIZACAO
     ,T1.DATA_INI_RETROATIVO
     ,T1.DATA_FIM_RETROATIVO
     ,T1.ATIVO
     ,T1.DATA_INSERT
     ,T1.DATA_UPDATE
     ,T1.APP_INSERT
     ,T1.APP_UPDATE
     ,T1.VALOR_PEDIDO_EC   
FROM dbo.TB_FATURA_ITEM_TRANSACAO  T1
UNION 
SELECT  
      T2.ID
     ,T2.ID_TRANSACAO
     ,T2.ID_FATURA
     ,T2.ID_FATURA_ITEM
     ,T2.ID_TIPO_SERVICO
     ,T2.ID_SERVICO
     ,T2.ID_TARIFA
     ,T2.DATA_REGISTRO_TRANSACAO
     ,T2.DATA_PAGAMENTO
     ,T2.CODIGO_REFERENCIA
     ,T2.DESCRICAO
     ,T2.MERCHANT_ID
     ,T2.ORDER_ID
     ,T2.IDENTIFICACAO_TITULO
     ,T2.CNPJ
     ,T2.NUMERO_AGENCIA
     ,T2.NUMERO_CONTA
     ,T2.QUANTIDADE_ITENS
     ,T2.VALOR_ITEM
     ,T2.VALOR_TRANSACAO
     ,T2.STATUS_TRANSACAO
     ,T2.DATA_INI_CONTABILIZACAO
     ,T2.DATA_FIM_CONTABILIZACAO
     ,T2.DATA_INI_RETROATIVO
     ,T2.DATA_FIM_RETROATIVO
     ,T2.ATIVO
     ,T2.DATA_INSERT
     ,T2.DATA_UPDATE
     ,T2.APP_INSERT
     ,T2.APP_UPDATE
     ,T2.VALOR_PEDIDO_EC  
from   dbo.TB_FATURA_ITEM_TRANSACAO_HST   T2

I was wondering using Schemabiding on View , but I was looking in some articles that is not recommended when you are using UNION .

Probably I got see which indexes I will use on it, right ?

…………………………………………………………

I applied the query that Gordon posted, I got huge performance on it. I reduced 15 minutes to 5 minutes. Anyway I got hustle on it to reduce more time.

Answer

The overhead in the view is due to duplication elimination. So, the first suggestion is to use union all, if there are no duplicates between the two tables. If you can, problem solved.

If not, you can use not exists. I might speculate that something like this would work:

select . . .
from dbo.TB_FATURA_ITEM_TRANSACAO  T1
union all
select . . .
from dbo.TB_FATURA_ITEM_TRANSACAO_HST Th
where not exists (select 1
                  from dbo.TB_FATURA_ITEM_TRANSACAO T1
                  where t1.id = t2.id 
                 );

This where clause in the subquery would contain all columns that need to be equal for a row to be considered a duplicate. I am speculating the id is sufficient.

Advertisement

Answer