Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

updating rows with xml Expand / Collapse
Author
Message
Posted Saturday, November 30, 2013 11:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:52 AM
Points: 115, Visits: 138
Hi,

I have a loop built which selects groups of records out based on different product groups.
The results are returned as XML which is then updated against the product group in a master product group table.
This is fine but now the product list is growing fast, the loop loops 100's of times and it's taking minutes to run now.

is there a way I can write this as a single update?

declare @products_xml xml
--get @groupid

while @group_id is not nul
begin

set @products_xml =
(
SELECT id, name, price
FROM product
WHERE product_group = @group_id
FOR XML RAW ('Product'), ROOT ('Product_Group');
)

update product_group set products_xml = @products_xml

--get next @groupid

end

any help welcome,
Eamon
Post #1518658
Posted Saturday, November 30, 2013 4:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 1,660, Visits: 5,223
Without any further info on how you are "getting the next @groupid"....



update pg
set pg.products_xml = x.xmldata
from product_group AS pg
cross apply (
SELECT id, name, price
FROM product
WHERE product.product_group = pg.product_group
FOR XML RAW ('Product'), ROOT ('Product_Group')
) AS x(xmldata)
where pg.product_group in (select some_product_groups from somewhere)





MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1518665
    Posted Saturday, November 30, 2013 9:48 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 5:17 PM
    Points: 36,002, Visits: 30,296
    I guess my question would be, why are you storing this data as XML to begin with? Why aren't you storing it as properly normalized data?

    --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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1518677
    Posted Sunday, December 01, 2013 5:09 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Tuesday, April 08, 2014 4:52 AM
    Points: 115, Visits: 138
    because a downstream process then takes the xml which feeds into a report.
    I can't amend the downstream process.
    Post #1518692
    Posted Sunday, December 01, 2013 6:20 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 5:17 PM
    Points: 36,002, Visits: 30,296
    EamonSQL (12/1/2013)
    because a downstream process then takes the xml which feeds into a report.
    I can't amend the downstream process.


    Oh my. I feel for you there.

    Did Magoo's answer help you solve your problem?


    --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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1518732
    Posted Monday, December 02, 2013 1:59 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Tuesday, April 08, 2014 4:52 AM
    Points: 115, Visits: 138
    Thank you mister.magoo, this works perfectly.

    Kind regards,
    Eamon

    Post #1518796
    Posted Monday, December 02, 2013 3:01 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:20 PM
    Points: 6,957, Visits: 12,716
    Would it be an option for you to create a view with the xml formatted result set (instead of storing it "physically")?
    Might help to deal with the data in a normalized format while still being able to present it in a xml format.

    This would also help to separate the data storage and the presentation layer. You could change the table structure without the downstream process even taking notice of it (as long as the result set of the view is unchanged).




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1518811
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse