January 19, 2010 at 9:35 am
Hi
I work in a company that provides an online service for the purchase of materials trough the internet. The complete application is supported by 2 different architectures. Both of them are 3 tiers. One uses ASP for the presentation layer, COM+ (Visual Basic 6) the other ASPX for the presentation and the code behind (C#) for the business logic. Both of them are built over SQL 2005. We are in the process of migrating everything to ASPX and C#. But until then we have to live with both.
I know that this is probably irrelevant for the issue in hands, but was just to give a little more information.
The problem that I will present is used by the ASP COM+ architecture.
The last week I have been dealing with performance issues in the SQL 2005 database.
There are several SP that receive a XML string and then uses the data to update rows in tables. These SP often give problems, sometimes the users have to wait a long period of time, and sometimes occurs deadlocks. We never know how many lines will be affected. I made the examples assuming 15000 rows.
I created 2 examples to test the performance of updating 15000 rows. On example uses XQuery and the other sp_xml_preparedocument. The construction of the XML is made in the same way for both examples.
So I need a table. The script is 01 - Create Table TABLEforXML.txt
Then I have to fill the table. For this I created the script 02 - Fill the table TABLEforXML.txt
The first approach was with cross apply (03 - Update TABLEforXML from XML with CROSS APPLY.sql)
The second one was with preparedocument (04 - Update TABLEforXML from XML with PREPAREdocument.sql)
What I found out this
With XQuey using cross apply the script takes more or less 1 minute and 16 seconds.
BEGIN UPDATE COM CROSS APPLY - 19/01/2010 16:19:24
END UPDATE COM CROSS APPLY - 19/01/2010 16:20:40
With preparedocument the script takes more or less 4 seconds.
BEGIN UPDATE COM PREPAREDOCUMENT - 19/01/2010 16:23:26
END UPDATE COM PREPAREDOCUMENT - 19/01/2010 16:23:30
And here comes the doubts
1 - I thought that cross apply would be faster than preparedocument. Obviously I was wrong (Or not???)
2 - My second concern is with the data that was updated in the table.
One of the columns that I'm updating is a date. With cross apply all the rows have the same value for that column (2010-01-19 16:19:24.497). Value that matches with the begin of the process. So my question is; what the hell was SQL doing during the remain 1 minute and 15 seconds
3 - With preparedocument not all the rows have the same value for that column. In 15000 rows I have 900 distinct values. Beginning at 2010-01-19 16:23:26.863 and ending at 2010-01-19 16:23:30.010. This value are according with the begin and end of the process.
All the scripts are in attachment.
Summing up.
Is the cross apply not used in the correct way?
What is in fact the best approach cross apply vs. preparedocument?
Is there an alternative way?
Thank you very much
January 19, 2010 at 10:30 am
I think the major issue is that you use the CROSS APPLY function in a way that forces SQL server to parse the xml string for each and every article.
I'd rather use an intermediate table and a "classic join". (If required, I would ad an index on Id to speed it up even more.)
Something like this:
SELECT
c.value('@id[1]','int') AS id,
c.value('@uni[1]','int') AS unit
INTO #intermed
FROM @xml.nodes('/root/art') t(c)
update t
set DateChanged = getdate(), Observations = 'UPDATE CROSS APPLY TMP',MeasureUnit = #intermed.unit
from TABLEforXML t inner join #intermed ON t.articleid = #intermed.id
To compare it to your solutions:
UPDATE CROSS APPLY TMP TABLE (new solution) - less than one sec
UPDATE COM CROSS APPLY - 2min 16 sec
UPDATE COM PREPAREDOCUMENT - 4 sec
Conclusion:
CROSS Apply works faster than OPENXML if used the right way...
January 19, 2010 at 11:36 am
Thank you very much. It worked perfectly
Best regards
Rui
January 19, 2010 at 11:56 am
April 14, 2011 at 10:47 pm
Thanks Bro,Great Work
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply