October 6, 2008 at 3:58 am
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
October 10, 2008 at 3:11 pm
This is a indirect cursor. For every row which needs to be updated you are executing the subqueries to find the XML.
Instead Join your temp table with respective tables and formulate xmls seperatly.
And then in your update statement simply assign.
October 11, 2008 at 5:16 am
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
October 13, 2008 at 4:33 pm
Well yes looping over each id and creating xml for them will be the same thing.
How about you have 3-4 select statements and which generates 3-4 xmls like this :-
..
...
..
...
likewise ... and then you play with XML. Anyways you want to consolidate the XMLs in the end.
Try this out. Should be faster and\or low subtree cost.
What is more imp is that you make it set based operation so that the cost remains the same and doesnt inc with inc in data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply