November 25, 2009 at 8:12 am
I've got what I thought would be a very simple XQuery against a variable and it's running quite slowly. In fact, the OPENXML version of the query that I was replacing runs in just over a second while this query below runs for four seconds. My assumption is that I've done something foolish, but I can't spot it. The one thing that I was told to avoid is using the '..\' mechanism, but if I remove that from the query, the performance doesn't change a bit. I can't provide data, but I've attached the execution plan (which looks pretty awful).
Please let me know what I've messed up here:
WITH XMLNAMESPACES
('http://xml.customweather.com/ns/rss/1.0'
AS cw,'http://www.w3.org/2003/01/geo/wgs84_pos#'
AS geo)
SELECT d.dsc.value('cw:city[1]', 'varchar(10)') AS city
,d.dsc.value('cw:city_name[1]','varchar(100)') as city_name
,i.item.value('geo:lat[1]','decimal(5,2)') as lat
,i.item.value('geo:long[1]','decimal(5,2)') as long
,i.item.value('cw:advisory[1]/@type','int') as ltype
,i.item.value('../title[1]','varchar(50)') as lname
,i.item.value('cw:advisory[1]/@description','varchar(50)') as dsc
,i.item.value('cw:advisory[1]/type[1]','int') as AlertTypeInd
,d.dsc.value('cw:localupdatetime[1]','varchar(100)') as LocalUpdateTime
,d.dsc.value('cw:note[1]','varchar(200)') as note
,i.item.value('title[1]','varchar(100)') as title
,i.item.value('cw:advisory[1]/@category','varchar(50)') as category
,d.dsc.value('cw:country[1]','varchar(100)') as country
,d.dsc.value('cw:state[1]','varchar(100)') as lstate
FROM --@inXML.nodes('/rss/channel/item[fn:not(cw:advisory[1]/@category eq "*")]') AS i ( item )
@inXML.nodes('/rss/channel/item') AS i ( item )
CROSS APPLY i.item.nodes('description') AS d( dsc )
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 25, 2009 at 11:17 am
Is there any case where items don't have descriptions? If that is NOT the case - you can do this in one pass and get rid of the cross apply (which in the end should be faster).
Also - do these actually have the same attribute names defined at different levels? Can't help but think that is making things harder.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 25, 2009 at 11:35 am
The XML isn't the cleanest in the world.
I did eliminate the CROSS APPLY as you suggested, but it's not seriously impacting performance. I'm just bummed because I told everyone and their brother that I'd have XQuery running faster than OPENXML, which, based on everything I've read, it should. My assumption is that I'm mucking something up in there, preventing the performance from being all it should be. I just can't find it.
It currently looks like this:
WITH XMLNAMESPACES
('http://xml.customweather.com/ns/rss/1.0'
AS cw,'http://www.w3.org/2003/01/geo/wgs84_pos#'
AS geo), S AS (
SELECT i.item.value('description[1]/cw:city[1]', 'varchar(10)') AS city
,i.item.value('description[1]/cw:city_name[1]','varchar(100)') as city_name
,i.item.value('geo:lat[1]','decimal(5,2)') as lat
,i.item.value('geo:long[1]','decimal(5,2)') as long
,i.item.value('cw:advisory[1]/@type','int') as ltype
,i.item.value('../title[1]','varchar(50)') as lname
,i.item.value('cw:advisory[1]/@description','varchar(50)') as dsc
,i.item.value('cw:advisory[1]/type[1]','int') as AlertTypeInd
,i.item.value('description[1]/cw:localupdatetime[1]','varchar(100)') as LocalUpdateTime
,i.item.value('description[1]/cw:note[1]','varchar(200)') as note
,i.item.value('title[1]','varchar(100)') as title
,i.item.value('cw:advisory[1]/@category','varchar(50)') as category
,i.item.value('description[1]/cw:country[1]','varchar(100)') as country
,i.item.value('description[1]/cw:state[1]','varchar(100)') as lstate
FROM --@inXML.nodes('/rss/channel/item[fn:not(cw:advisory[1]/@category eq "*")]') AS i ( item )
@inXML.nodes('/rss/channel/item') AS i ( item )
Oops. Forgot to add the code.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 28, 2011 at 10:41 am
Though dated a little, this thread is the closet to my project I've been able find. Any more info/progress to report?
Mike
MacroTrenz.Com
LinkedIn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply