Post reply

XQuery result to temprary table

  • August 16, 2007 at 3:53 am

    #68913

    Hi,

    I've following XQuery statement accessing a diffgram :

    With XMLNameSpaces('urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr )

    SELECT T1.c.value('@diffgr:id[1]','nvarchar(50)') as act_PLU,

    T1.c.query('.') as new_data,

    T.b.value('@diffgr:id[1]','nvarchar(50)') as old_PLU ,

    T.b.query('.') as old_data

    into #ddtable

    FROM @diffgram.nodes('/diffgr:diffgram/diffgr:before/Preset_PLU') T(b)

    join @diffgram.nodes('/diffgr:diffgram/DocumentElement/Preset_PLU') T1(c)

    on T1.c.value('@diffgr:id[1]','nvarchar(50)') = T.b.value('@diffgr:id[1]','nvarchar(50)')

    It works as expected but the runtime is tremendous: It runs for about 23 sec. ( diffgram contains about 2000 rows ).

    If I do not materialize the XQuery by removing the "into #ddtable" statement the same query runs 0.3 sec.

    Does anybody has a clue what I'm doing wrong or what could be done better ?

    Thanks for any help,

    Karsten

  • August 16, 2007 at 10:31 am

    #727430

    Check out the performance difference of creating the temporary table first and the INSERT INTO it...


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • August 17, 2007 at 12:19 am

    #727546

    Hi Adrian,

    thanks for your suggestion but I've already done this without any speedup ...

    Meanwhile I've worked around this issue but nevertheless I'm curious about this behaviour...

    Karsten

  • August 17, 2007 at 8:18 am

    #727655

    Check my question around in the XML forum somewhere.  I had similar problems with performing inserts from XML data.

    I ended up having to select the portion of the XML I was interrested in out of the main XML data into another XML variable, then selecting from that smaller XML variable into my table.  Sped it up by a factor of about 30.  This of course wouldn't apply if you need the entire XML out.

    My findings was that it made no difference if I created a temp table, or selected into.  Or wheter they where # tables or table variables.  Something is wrong with the way SQL handles XML data inserted into a normal table. 

  • August 29, 2007 at 4:43 am

    #730037

    Hi Anders,

    in my case the issue is solved!

    Microsoft has released a "cumulative update package 3 for SQL Server 2005 SP2" that resolves this problem ( SQL Server Bug# 50001639 ).

    A download link it mailed to you by request as stated on http://support.microsoft.com/kb/939537/en-us

    Just wanted to share my findings with you who had the same problem.

    Greetings,

    Karsten

Viewing 5 posts - 1 through 4 (of 4 total)

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