March 27, 2007 at 10:34 am
Your article is very interesting from the viewpoint of presenting a different way to deal with the classic order entry problem. However, it would be useful if you would show some timings for the old way vs. the new way. Specifically:
What is the elapsed time for a single order from a single client? How does the time degrade as the number of users/orders increases?
What is the cost to the SQL Server for each method (CPU, Duration, Reads, Writes)? How does that change as the number of transactions increases?
Again, the technique is interesting, but I think you should show how it compares to the "standard" method from a timing and resources point of view.
March 27, 2007 at 10:42 am
Hi Paul,
I will definitely collect some performance data and will post it soon.
Jacob
.
March 27, 2007 at 11:48 am
The bit that concerns me is the BOL entry that warns that this takes 1/8th of the server memory. So a 16GB box is going to lose 2GB RAM just for opening a World document.
In SQL2000 I'm pretty sure that the XML parser is MSXML3 where as the most up-to-date version is MSXML6.0.
It's a shame because its an elegant solution let down by the underlying technology.
March 27, 2007 at 11:59 am
This article is clean and clear. I hope there is a part two that will shed more light on related topics. This would be very helpful for a DBA greener like me. Thanks.
Amarado
March 27, 2007 at 12:43 pm
Amarado,
I found (by accident) that if you go to the top of this page and click on "Next Topic" it will take you to what appears to be all of the articles in this series. Look quick before they change it!
March 27, 2007 at 1:14 pm
Thanks Paul
I was able to locate all the previous articles and I am going through them. They are very helpful. Thanks to all the experts out there for making DB life a little easier.
Amarado
March 27, 2007 at 4:16 pm
This is my bread and butter method now. 2000 = OPENXML.
2005 has some different xml functions. Those should be explored.
..
One of the benefits is that indexes are updated after the entire set of data is inserted or updated.
Another is a "all or nothing" insert or update. You don't orphan any data.
I've added a twist, where I can insert or update, based on the match of a primary key.
Here is an example:
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
DECLARE @errorTracker int -- used to "remember" the @@ERROR
DECLARE @updateRowCount int
DECLARE @insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) , --<<UDF to convert xmldate to tsql date
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit)
EXEC
sp_xml_removedocument @hdoc
--select * from @titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @titleupdate
SET
alreadyExists = 1
FROM
@titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @updateRowCount = @@ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@titleupdate
WHERE
alreadyExists = 0
Select @insertRowCount = @@ROWCOUNT
select @numberRowsAffected = @insertRowCount + @updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
March 27, 2007 at 11:47 pm
Hi,
In part III of this series, I have presented the sample code which handles the insert/update/delete scenario. When a sales order is edited, a few new items may be added, a few items may be modified and/or a few items may be deleted. The sample code in Part III shows an example of how to handle it.
.
December 21, 2011 at 12:30 am
Hi, very good series of articles, I'm starting from the first one, hope once reach the last one of the serie I can apply all I learned on MSSQL 2008R2 where I'm working on.
I found an issue, at least on this first part. The links of the source code does not lead to the correct place, no code is shown.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply