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

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.

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.

thanks!

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.

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!

I got it. thanks a lot