SQL Server Performance

operating xml with nodes() method directly v.s. using temp table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by davidshen84, Nov 9, 2008.

  1. davidshen84 New Member

    Hi,
    I have a SP that would consume a xml as an input parameter and insert the data into tables. I am using the nodes() method to query the data in the xml doc. , but I am not sure about its performance. Especially, I need to use 'join' to filter the data. I want to know if I dump the xml doc. into a temp. table, and then insert the data into my tables, would this get better performance? How much performance improve would I have?
    PS: the xml doc. parameter is attribute centric, and may not be huge in volume.
  2. mufford New Member

    Hi davidshen84,
    I would recommend the use of a temporary table. You mentioned "tables"... if you're dumping this data into morethan one table, or putting part of the data in one table and the restin another, then you'd definitely get improved performance by storing the data in the temp table and inserting from there. If nothing else, you should get a performance boost by having an index on your joining criteria.
  3. davidshen84 New Member

  4. satya Moderator

    Bear in mind that make sure to size up the TEMPDB when you are using this way, as it will use the TEMPDB extensively.
  5. davidshen84 New Member

    sorry, but what you mean by saying "size up the TEMPDB"?
    increase the size of the tempdb, or clean up the tempdb? How can I do that? I think this database is maintained by the SQL server itself.
  6. mufford New Member

    To find out how much free space you have in tempdb, execute this command:Use tempdb;
    Go
    Execute
    sp_spaceused;
    If you do not have much unallocated space available, you probably need to increase the size of your tempdb. Assuming you perform this task in SSMS, nagivate to the System Databases folder in the Object Explorer. Right click on tempdb, and go to the Files option on the left hand side. There you will find and be able to change the size of your tempdb. You shouldn't have this set to something really low (i.e. 10MB), or you'll probably find a lot of wasted server resources spent on auto-growing this database after every SQL Server restart.
    You can also modify the size programmatically:Use master;
    Go
    Alter
    Database tempdb
    Modify File
    (
    NAME = temp -- may need to change
    , SIZE = 100MB -- change as appropriate
    );
    Go
    HTH!
  7. davidshen84 New Member

    I got it. thanks a lot :)

Share This Page