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

  • You can always see if the FOR XML PATH part is the major issue by executing the query without that part; if the execution time is almost the same, then that's not where the major issue is. Also, for large result sets, it can help to send the output to a temporary table instead of to your screen, as this eliminates the time to send all the data to your machine.
    – RDFozz
    Sep 21 '17 at 20:30

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]
    (tp_terminalId, tp_POSId, status);

-- Currently scanning 21.5 million rows to return zero    
CREATE INDEX give_me_a_name
ON [wts].[KBETicketTransactionLog]
    (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]
    (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]
    (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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.