Optimizing query. FOR XML PATH or some alternatives? Is there any way to optimize the FOR XML PATH statement? Or maybe I should use another approach?

Current approach is not acceptable at all. It takes minutes. I know view is a really big union and it takes time to consume that, but maybe is another way...

Here is the query:

SELECT t.serialNumber as TVM
  ,[issuanceDate] as transactionDate
  ,ioy.tvmTransactionId as TVM_TRANS_ID
  ,STUFF((SELECT ', ' + stv.carrierSN
    FROM HERMES.wts.v_SaleTransactionView as stv
    WHERE stv.tvmTransactionId = ioy.tvmTransactionId
    and stv.serialNumber = 'M040'
    FOR XML PATH ('')), 1, 2, '') as [serial_numbers]
  FROM [hermes].[wts].[IOYLog] ioy
  left join Hermes.hermes.Terminals t on ioy.tp_terminalId = t.tp_terminalId
  left join hermes.hermes.POS p on t.tp_POSId = p.tp_POSId
  left join [Hermes].[wts].[IOUPaymentStatus] [is] on [is].[tp_paymentStatusId] = ioy.status
  WHERE [is].includeInReports = 1 
  and (issuanceDate BETWEEN '2017/09/01' AND '2017/09/08') and (t.serialNumber = 'M040')
  ORDER BY transactionDate


Execution plan: https://www.brentozar.com/pastetheplan/?id=Sk7i98Wjb
Looking at this in Sentry One Plan Explorer, there are four major indexing issues, all associated with index scans hiding an expensive residual predicate:















The following four indexes will eliminate these scans and speed up this execution plan:

--- Currently scanning 7 million rows to return 11
CREATE INDEX give_me_a_name
ON [wts].[Prepaid_TransactionLog]
    ([tvmTransactionId])
INCLUDE
    (tp_terminalId, tp_POSId, status);

-- Currently scanning 21.5 million rows to return zero    
CREATE INDEX give_me_a_name
ON [wts].[KBETicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (carrierSN, tp_terminalId, tp_POSId, status);

-- Currently scanning 6.25 million rows to return 12
CREATE INDEX give_me_a_name
ON [wts].[Intercity_TicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (formType, status, tp_terminalId, tp_POSId);


The fourth index is a little more complicated since the key involves a conversion to integer to match [wts].[IOYLog].[tvmTransactionId]. This means either altering the existing column, or providing a computed column as follows:

-- New computed column, should be instant to add (metadata only)
ALTER TABLE [mazovian].[Transaction]
ADD [SequenceAsInteger] AS CONVERT(integer, [Sequence])

-- Currently scanning 61.6 million rows to return 70
CREATE INDEX give_me_a_name
ON [mazovian].[Transaction]
    ([SequenceAsInteger])
INCLUDE
    (Id, Sequence, Terminal_id, Detail_id, Location_id);


Adding those four indexes should speed up the query significantly. As with all indexing changes, you should assess them on a test system before deciding on deployment.



There are a number of other small indexing improvements you could consider to avoid the Key Lookups in the plan:


add [status] to the existing index [IOYLog].[IX_IOYLog_tp_terminalId_tvmTransactionId_issuanceDate_owedAmount]
add carrierSN, tp_POSId, and status to index [TransportTransactionLog].[IX_TransportTransactionLog_tp_terminalId_tvmTransaction_KK]
add End_Status to index [Transaction_Distribution].[I_mazovian_Transaction_Distribution_Ticket_id]