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 tmpTBL
SET
col_xml1  = (SELECT column_1,  column_2, column_3, column_4, column_5
FROM 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_5
FROM salary_hisotry shist
inner join pay_roll
on shist.emp_id = pay_roll.emp_id
where 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 thist
where emp_id = tmpTBL.emp_id 
FOR XML PATH('emp_transfer_history'), TYPE),
col_xml4  = (SELECT column_1,  column_2, column_3, column_4, column_5
FROM sales_hisotry sale_hist
where 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.