Optimizing SQL Query: FOR XML PATH or Alternatives

concatexecution-planperformancequery-performancesql server

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

Best Answer

Looking at this in Sentry One Plan Explorer, there are four major indexing issues, all associated with index scans hiding an expensive residual predicate:

enter image description here enter image description here

enter image description here enter image description here

enter image description here enter image description here

enter image description here enter image description here

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]
Related Question