Query Performance - Updating self referenced table with XML data type columns

Hi,

To give you a better understanding, I am just trying to create a replica of my situation, so kindly ignore any syntax errors:

In the following temporary table, i have inserted 1000 employee ids, based on some previous different selections: XML columns are null at the moment


create table #tmpEmpXML(empid uniqueidentifier,col_xml1 xml,col_xml2 xml,col_xml3 xml,col_xml4 xml)


Now i am updating the xml columns of same table with co-related sub-queries.

UPDATE tmpTBLSETcol_xml1 = (SELECT column_1, column_2, column_3, column_4, column_5FROM department_hisotry dhist inner join dept on dhist.dept_id = dept.dept_id where emp_id = tmpTBL.emp_id FOR XML PATH('emp_dep_history'), TYPE),col_xml2 = (SELECT column_1, column_2, column_3, column_4, column_5FROM salary_hisotry shistinner join pay_rollon shist.emp_id = pay_roll.emp_idwhere emp_id = tmpTBL.emp_id FOR XML PATH('emp_sal_history'), TYPE),col_xml3 = (SELECT column_1, column_2, column_3, column_4, column_5 FROM transfer_hisotry thistwhere emp_id = tmpTBL.emp_id FOR XML PATH('emp_transfer_history'), TYPE),col_xml4 = (SELECT column_1, column_2, column_3, column_4, column_5FROM sales_hisotry sale_histwhere emp_id = tmpTBL.emp_id FOR XML PATH('emp_sales_history'), TYPE)FROM#tmpEmpXML tmpTBL


After this update, I am wrapping this whole temporary table along with updated xmls, into a single output XML. This output XML will be used in the subsequent number of related operations on that XML which is already very well define and cannot be changed at this stage.


In my situation, there are number of other complex joins involved in the inner queries. All relevant key and non-key indexes are already there.

According to the database tuning advisor, this UPDATE code is taking almost 85% of whole SP execution time which is 40-55 seconds, depends on data in tempTable


NOW QUESTION is :- What are possibilities OR any check-list OR ways to improve the performance (speed-up things) and reduce this processing time ?

Cheers

Try creating the temp table for each subquery with FOR XML and use the temp table in your update at once or one update for each temp table...

Your statement is giving some double meanings me. Kindly explain a bit more please
Cheers

I would assume that updating an XML column is slower than updating a plain data column. Why not have separate columns in the temp table for each XML item?
And why not update from one XML source at a time?

Well !!!
In that case, there will be three different insert statements (3 loops for same purpose) to prepare three separate temporary tables
and
then join all four tables to UPDATE the final one
So would this not be an expensive alternate, infact?
what do you think ?
waiting
Cheers

I suggest to test, see what effect it has on performance. You never know till you try. Trial and error.