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