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 momentcreate 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 tmpTBLAfter 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 tempTableNOW QUESTION is :- What are possibilities OR any check-list OR ways to improve the performance (speed-up things) and reduce this processing time ?CheersSilent VoiceBill Gates, MVP
Query Performance - Updating self referenced table
Posted - 2008-10-06 : 06:06:51
