Saving a Sales Order Part 1

  • 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.

  • Hi Paul,

    I will definitely collect some performance data and will post it soon.

    Jacob

    .

  • 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.

  • 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

  • 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!

  • 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

  • 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

     

  • 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.

    .

  • 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 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply