Alternative to SQL Cross apply because of performance issues

5 pts.
Tags:
SQL Server
XML
I am stuck in a bit rut. I have an XML and in SQL Server. I want to get all the values of FBC separated by commas. Name of the table is Table1 Name of the column in which XML is stored is FareDetails BookingID and ID are other columns of Table 1.
SELECT
                (PTSD.PSTDNode.value('(FBC)[1]', 'VARCHAR(1024)')  + ',') [text()]
            FROM
                [Table1]
                CROSS APPLY [FareDetails].nodes('/AirFareInfo/PTSDPFS/PTSD') PTSD(PSTDNode)
            WHERE
                [BookingID] = 123
            ORDER BY
                [AirTraveler].ID ASC
            FOR XML PATH ('')
But I have million records in the DB and this is taking a huge toll on performance. Please suggest better solution. The xml is as follows:
<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PT>Flight</PT>
  <FPMID>0</FPMID>
  <PTID>1</PTID>
  <FS>
    <CID>2</CID>
    <Value>0</Value>
  </FS>
  <TF>
    <CID xsi:nil="true" />
    <Value>0</Value>
  </TF>
  <VF>
    <CID>2</CID>
    <Value>0</Value>
  </VF>
  <VD>
    <CID>2</CID>
    <Value>0</Value>
  </VD>
  <VCR xsi:nil="true" />
  <VC>
    <CID>2</CID>
    <Value>0</Value>
  </VC>
  <VFC>
    <CID>2</CID>
    <Value>0</Value>
  </VFC>
  <VST />
  <VIT />
  <AAPFVDR xsi:nil="true" />
  <CC>
    <CID>2</CID>
    <Value>0</Value>
  </CC>
  <D>
    <CID>2</CID>
    <Value>514.15</Value>
  </D>
  <PD>
    <CID>2</CID>
    <Value>0</Value>
  </PD>
  <EBF>
    <CID>2</CID>
    <Value>0</Value>
  </EBF>
  <CST>
    <DL>
      <ATRID>13</ATRID>
      <OB>
        <CID>2</CID>
        <Value>74.04</Value>
      </OB>
      <OC>
        <CID>2</CID>
        <Value>0.00</Value>
      </OC>
      <OS>
        <CID>2</CID>
        <Value>0.00</Value>
      </OS>
      <OF>
        <CID>2</CID>
        <Value>50.83</Value>
      </OF>
      <OP>
        <CID>2</CID>
        <Value>0.00</Value>
      </OP>
      <C>
        <CID>2</CID>
        <Value>0</Value>
      </C>
      <IBF>false</IBF>
      <D>2014-06-09T14:57:53.521Z</D>
    </DL>
  </CST>
  <CIT />
  <CRMR xsi:nil="true" />
  <CRM>
    <CID>2</CID>
    <Value>0</Value>
  </CRM>
  <TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>75.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="Passenger Service Fee">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>146.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>1681.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="Cute Fee">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>50.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="Government Service Tax">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>151.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>833.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="Passenger Service Fee">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>1132.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>76.00</Value>
    </Amount>
  </TL>
  <TL ATC="Tax" PC="" DEN="Government Service Tax">
    <TID xsi:nil="true" />
    <Amount>
      <CID>2</CID>
      <Value>148.00</Value>
    </Amount>
  </TL>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
      <ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
      <ATSID xsi:nil="true" />
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
      <ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
      <ATSID xsi:nil="true" />
    </PTSD>
  </PTSDPFS>
  <RuleDetails>
    <TRS xsi:nil="true" />
    <PP xsi:nil="true" />
    <II xsi:nil="true" />
    <LTD xsi:nil="true" />
  </RuleDetails>
</AirFareInfo>
Output in this case should be AP,AP Any help would be kindly appreciated. Thanks.

Software/Hardware used:
Sql server

Answer Wiki

Thanks. We'll let you know when a new response is added.

Check to see if there are any indexes/views on your SQL table by the key field being selected. Creating one can speed thing up but even at a million records, it should not be a big deal performance wise.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: