SQL Server 2008 Table Valued Parameters Performance

  • Scott Zurolo

    SSC Enthusiast

    Points: 117

    Comments posted to this topic are about the item SQL Server 2008 Table Valued Parameters Performance

  • Phil Factor

    SSCoach

    Points: 19913

    Thanks for that. An interesting read, and I like seeing performance figures.

    In the non-XML solution you recommend, how does one get the bulk updates from the client application to the server, and into the input table in one database call? In your scenario you are 'updating multiple order detail rows in a data grid at once. The user needs to update the price and quantity for 250 order line items.' I can see how to create the XML to do the update and call the stored procedure in the first two examples, but I don't understand how to stock the user defined table type with the data so easily. Would this be an ASP.NET bulk insert or a multiple-value insert? (INSERT INTO tempTable (FirstCol, SecondCol) VALUES (1,'First'), (2,'Second'), (3,'Third'), (4,'Fourth'), (5,'Fifth') ....) If this is the case, wouldn't the 'traditional' approach of using a temporary table be just as fast? Apologies in advance if I've missed something.

    Best wishes,
    Phil Factor
    Simple Talk

  • finizi

    Grasshopper

    Points: 15

    And what about using sp_xml_preparedocument to parse XML ?

  • jude.pieries

    Default port

    Points: 1438

    Using XML as a input parameter

    - I wouldn’t use a XML as a input parameter to update a table; mainly to avoid the XML reader operation.

    - This approach will lock the updating table for extensive period which is not acceptable

    Watch out if you are attempting to update a large table using a table value parameter, the chances are that you will lock the updating table for a lengthy period of time causing longer transaction response times on your application which won’t be acceptable.

    Some details on table –value parameters

    http://msdn.microsoft.com/en-us/library/bb510489.aspx

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hi Scott

    Nice article about a new technology that is way too less used till now (in my opinion).

    @Phil_Factor

    To send data from .NET client side to a table-valued parameter, you can use either a DataTable (I don't like this due to its huge overhead) or a IEnumerable<SqlDataRecord> (my preference).

    Here's an article from SSC:

    Streaming Data Into SQL Server 2008 From an Application[/url]

    And here are further articles from my blog:

    Table-Valued Parameters - A Performance Comparison

    Table-Valued Parameters

    Hope this helps

    Flo

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hi finizi

    finizi (7/8/2010)


    And what about using sp_xml_preparedocument to parse XML ?

    Is marked as deprecated since SQL Server 2005 and should not be used any more. Further,

    • The old SQL Server 2000 XML functions have been restricted to
    • Old XML features have been provided over extended procedures (like sp_xml_preparedocument). New XML support is a native feature of SQL Server
    • New XML features should be much faster than old once.
    • Old once have been restricted to 8000 characters

    Greets

    Flo

  • einman33

    SSCrazy

    Points: 2753

    jude.pieries (7/8/2010)


    Using XML as a input parameter

    - I wouldn’t use a XML as a input parameter to update a table; mainly to avoid the XML reader operation.

    - This approach will lock the updating table for extensive period which is not acceptable

    Watch out if you are attempting to update a large table using a table value parameter, the chances are that you will lock the updating table for a lengthy period of time causing longer transaction response times on your application which won’t be acceptable.

    Some details on table –value parameters

    http://msdn.microsoft.com/en-us/library/bb510489.aspx

    Do you have an example of an update statement holding a table lock where the sole reason for the lock escalation was the use of the xml parsing? I have seen normal pessimistic locking behaving when shredding xml in the data tier for use in +1 crud scenarios.

  • WayneS

    SSC Guru

    Points: 95342

    Excellent article. The only thing I think would have been nice to add (and which has been covered in this discussion forum) is how to pass the data into the table from the application.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Scott Zurolo

    SSC Enthusiast

    Points: 117

    In the calling ASP.NET application you can create a ADO.NET datatable object and populate the datatable with the data rows from the grid. The datatable would be passed into the stored procedure as a single parameter. The parameter would be declared as DB type Structured. The code would look something like this:

    DataTable OrderDetailsDT

    //Add data to the datatable

    SqlCommand Cmd = new SqlCommand( "UspInsertOrderDetails",conn);

    Cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter Param = Cmd.Parameters.AddWithValue(

    "@OrderDetails", OrderDetailsDT);

    Param.SqlDbType = SqlDbType.Structured;

    insertCommand.ExecuteNonQuery();

  • Bradley Deem

    SSCrazy

    Points: 2565

    Hey Scott, thanks for the article. Florian, thanks for the blog links those were great.

    I'm curious how the XML method would perform when used with XML Schema Collections. I'm guessing it'd be better, but not as good as TVP.

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Bradley Deem (7/8/2010)


    Florian, thanks for the blog links those were great.

    Thanks for the feedback 🙂

  • Phil Factor

    SSCoach

    Points: 19913

    Thanks Scott, I must read up on my ADO.NET! Thanks too, Florian. I missed those blog posts. I must try it out. I can see this making a great difference to performance.

    Best wishes,
    Phil Factor
    Simple Talk

  • SQLRNNR

    SSC Guru

    Points: 281205

    Nice article. Thanks for sharing with us.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • kinzent

    Mr or Mrs. 500

    Points: 561

    🙂

    Thank you . I get new things again.

    ---------------------------------------
    Thorn Bird...

  • Steven Bates

    Grasshopper

    Points: 17

    Some of the poor performance is down to your xpath and the use of both .vaue and .query.

    This is somewhat quicker. (Though it is never going to be as quick as using a table variable parameter)

    ALTER PROCEDURE [dbo].[UpdateTableViaXML3]

    (

    @Data xml

    )

    AS

    SET NOCOUNT ON

    DECLARE @ErrMsg varchar(100),

    @ErrNo int,

    @ErrSeverity varchar(100)

    BEGIN TRY

    UPDATE PODetailTest

    SET OrderQty = T.PO.value('(OrderQty/text())[1]', 'smallint') ,

    UnitPrice = T.PO.value('(UnitPrice/text())[1]','money')

    FROM @Data.nodes('Root/PurchaseOrderDetail') AS T(PO)

    WHERE T.PO.value('(PurchaseOrderDetailID/text())[1]','int') = PODetailTest.PurchaseOrderDetailID

    END TRY

    BEGIN CATCH

    SELECT @ErrMsg = 'An error occurred in stored procedure ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),

    @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)

    END CATCH

    GO

Viewing 15 posts - 1 through 15 (of 26 total)

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