using cross apply to find different values trough xml files eficiency

Question

using cross apply to find different values trough xml files eficiency

montserrat.deza

Right there with Babe

Points: 725
More actions
October 20, 2017 at 2:12 pm

#340897

Hello Experts,
I am trying to run the following query:
           SELECT CAST(t.nodes.query('data(item)') as varchar(max)) 'Item',
                          CAST(t.nodes.query('data(bp_orno)') as varchar(max)) 'Order',
                          CAST(t.nodes.query('data(rpos)') as varchar(max)) 'Order Line',
                          CAST(t.nodes.query('data(wh)') as varchar(max)) 'WH',
                          CAST(t.nodes.query('data(qty)') as varchar(max)) 'Quantity'
                          e.Export AS Transaction_Date,
                         FROM ExportS e (nolock)
                         CROSS APPLY e.SignalData.nodes('fl/app/data/rec') AS t(nodes)
                         where e.SignalTypeId in (29) and
                          CAST(t.nodes.query('data(wh)') as varchar(max)) in   ('VALUE1',
    'VALUE2',
    'VALUE3',
    'VALUE4',
    'VALUE5',
    'VALUE6',
    'VALUE7',
    'VALUE8',
    'VALUE9',
    'VALUE10',
    'VALUE11',
    'VALUE12',
    'VALUE13',
     'VALUE14',
     'VALUE15',
     'VALUE16',
     'VALUE17',
    'VALUE18')
But is taking forever to find these values on the xml files we have in our database. Our database is huge, so I don't think that I am using the most efficient way to find this data. Could you advise on which method will be the best way to find all data we need for the 18 values on the signals (XML Files)?
Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 1

montserrat.deza - Friday, October 20, 2017 2:12 PM
Hello Experts,
I am trying to run the following query:
           SELECT CAST(t.nodes.query('data(item)') as varchar(max)) 'Item',
                          CAST(t.nodes.query('data(bp_orno)') as varchar(max)) 'Order',
                          CAST(t.nodes.query('data(rpos)') as varchar(max)) 'Order Line',
                          CAST(t.nodes.query('data(wh)') as varchar(max)) 'WH',
                          CAST(t.nodes.query('data(qty)') as varchar(max)) 'Quantity'
                          e.Export AS Transaction_Date,
                         FROM ExportS e (nolock)
                         CROSS APPLY e.SignalData.nodes('fl/app/data/rec') AS t(nodes)
                         where e.SignalTypeId in (29) and
                          CAST(t.nodes.query('data(wh)') as varchar(max)) in   ('VALUE1',
    'VALUE2',
    'VALUE3',
    'VALUE4',
    'VALUE5',
    'VALUE6',
    'VALUE7',
    'VALUE8',
    'VALUE9',
    'VALUE10',
    'VALUE11',
    'VALUE12',
    'VALUE13',
     'VALUE14',
     'VALUE15',
     'VALUE16',
     'VALUE17',
    'VALUE18')
But is taking forever to find these values on the xml files we have in our database. Our database is huge, so I don't think that I am using the most efficient way to find this data. Could you advise on which method will be the best way to find all data we need for the 18 values on the signals (XML Files)?
Thank you!

I don't know much about XML but, rumor has it, adding an XML index will speed things up quite a bit. I'm convinced that behind the scenes, it's pre-materializing the data as a bit of an EAV.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 2

This is a very inefficient way of querying the XML, can you post a sample of the XML so we can help you out here?
😎

Jeff, I don't think an XML index would help much as the query is so inefficient, more like sprinkling sugar on cat food as our friend used to say.

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 3

Eirikur Eiriksson - Saturday, October 21, 2017 3:13 AM
This is a very inefficient way of querying the XML, can you post a sample of the XML so we can help you out here?
😎
Jeff, I don't think an XML index would help much as the query is so inefficient, more like sprinkling sugar on cat food as our friend used to say.

Man, I miss him. Dwaine Camps was definitely one of the good guys on this and other forums and a personal long distance friend to boot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 4

Jeff Moden - Saturday, October 21, 2017 4:00 PM
Eirikur Eiriksson - Saturday, October 21, 2017 3:13 AM
This is a very inefficient way of querying the XML, can you post a sample of the XML so we can help you out here?
😎
Jeff, I don't think an XML index would help much as the query is so inefficient, more like sprinkling sugar on cat food as our friend used to say.
Man, I miss him. Dwaine Camps was definitely one of the good guys on this and other forums and a personal long distance friend to boot.

Ditto!
😎

montserrat.deza Right there with Babe Points: 725 More actions · Answer 5

Thank you,
Here is the sample of the file.

<app>

<conv_id />

<data>

<rec>

<rec_date>20140812</rec_date>

<rec_time>070009</rec_time>

<bp_orno>85928487</bp_orno>

<bp_pono>000000051</bp_pono>

<order>

<cpo_pos>1</cpo_pos>

</order>

<skids>

<skid>

</skid>

</skids>

<hdr_attrs>

<attval>Global Support</attval>

</attribute>

<attval>ctsb2b-support@TEST.COM</attval>

</attribute>

</attribute>

<attval>Anne Guidry</attval>

</attribute>

<attval>anne.guidry@TEST.COM</attval>

</attribute>

</attribute>

</hdr_attrs>

</attributes>

<rec_attr>

</rec_attr>

</rec>

</data>

</app>

</flxint>

montserrat.deza Right there with Babe Points: 725 More actions · Answer 6

montserrat.deza

Right there with Babe

Points: 725

October 23, 2017 at 9:16 am

#1964811

I attached the file to see the xml structure....

Thank you

montserrat.deza Right there with Babe Points: 725 More actions · Answer 7

montserrat.deza

Right there with Babe

Points: 725

October 23, 2017 at 9:18 am

#1964812

Here it is