Download
1 / 34

Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE - PowerPoint PPT Presentation


  • 141 Views
  • Uploaded on

Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE. Tobias Ternstrom Program Mgr., SQL Server Engine Microsoft Corporation DAT 320. Agenda. Passing a set of data to SQL Server Adding MERGE to the equation . Passing a Set of Data to SQL Server.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE' - lawson


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Optimizing microsoft sql server 2008 applications using table valued parameters xml and merge

Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE

Tobias Ternstrom

Program Mgr., SQL Server Engine

Microsoft Corporation

DAT 320


Agenda
Agenda Table Valued Parameters, XML, and MERGE

  • Passing a set of data to SQL Server

  • Adding MERGE to the equation


Passing a set of data to sql server
Passing a Set of Data to SQL Server Table Valued Parameters, XML, and MERGE

  • N rows = N executed statements

  • N rows = 1 executed statement


Passing a set of data to sql server1
Passing a Set of Data to SQL Server Table Valued Parameters, XML, and MERGE

  • N rows = N executed statements

  • One client server roundtrip per execution

  • All executions in one batch


Passing a set of data to sql server2
Passing a Set of Data to SQL Server Table Valued Parameters, XML, and MERGE

  • N rows = 1 executed statement

    • Pass the data as a delimited list

    • Pass the data as XML

    • Pass the data as Table Valued Parameter

  • Other options

    • Managed bulk copy to a table

    • Pass data as separate arguments (current limit is 2,100)


Examples
Examples Table Valued Parameters, XML, and MERGE

  • In the examples, we will be passing a set of items to the database for storage

    • Example – “Store the following 1,000 items”

  • Examples we’ll use:

    • Stored Procedures

    • C# & ADO.NET


Pass the data as a delimited l ist
Pass the Data as a Delimited Table Valued Parameters, XML, and MERGEList

// C#

cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "Test.spDelimitedString";cmd.Parameters.AddWithValue("@Values", @"…|…|…

…|…|…

…|…|…");cmd.Execute…;

-- What happens on the server?

EXEC Test.spDelimitedString @Values = '…|…|…

…|…|…

…|…|…';


Pass the data as a delimited l ist1
Pass the Data as a Delimited Table Valued Parameters, XML, and MERGEList

  • To get the best performance, we need to use a SQLCLR Table Valued Function

    • Pros:

      • Performance is good

      • No exposure to SQL Injection

    • Cons:

      • Requires SQLCLR to be enabled on the instance

      • The set of data is not strongly typed

      • Cumbersome implementation

        • Can be simplified by creating one TVF per “list type”


Pass the data as xml
Pass the Data as XML Table Valued Parameters, XML, and MERGE

// C#

cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "Test.spXML";cmd.Parameters.AddWithValue("@Values", doc.OuterXml);cmd.Execute…;

-- What happens on the server?:

EXEC Test.spXML @Values = N'<Orders><Order…


Pass the data as xml1
Pass the Data as XML Table Valued Parameters, XML, and MERGE

  • Pros

    • Strongly typed (if you use an XML Schema Collection)

    • Performance is OK

    • No exposure to SQL Injection

    • A very good option if your data is already XML!

    • Great flexibility; remember XML allows for hierarchies

  • Cons

    • Performance is good but not the best

    • Requires knowledge about XML

    • Less cumbersome than the delimited list but still somewhat cumbersome


  • Pass the data as a table valued parameter
    Pass the Data as a Table Valued Parameter Table Valued Parameters, XML, and MERGE

    // C#

    cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "Test.spTVP";var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);p.TypeName = "Test.OrderTableType";p.Value = dataTable;

    cmd.Execute…;

    -- What happens on the server?:

    DECLARE @Values Test.OrderTableType;

    INSERT @Values …

    EXEC Test.spTVP @Values = @Values;


    Pass the data as table valued parameter
    Pass the Data as Table Valued Parameter Table Valued Parameters, XML, and MERGE

    • Pros

      • Strongly typed

      • No exposure to SQL Injection

      • Performance is great!

      • Very easy to use, both on client and server side

  • Cons

    • Less flexible than XML; may require you to pass multiple TVPs where one XML parameter would have been enough

    • Allows for streaming but only to the server


  • Pass the data as a table valued parameter streaming
    Pass the Data as a Table Valued Parameter Table Valued Parameters, XML, and MERGE Streaming

    // C#

    class MyStreamingTvp : IEnumerable<SqlDataRecord> {…

    }

    …cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "Test.spTVP";var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);p.TypeName = "Test.OrderTableType";p.Value = new MyStreamingTvp(…);

    cmd.Execute…;-- What happens on the server?:

    DECLARE @Values Test.OrderTableType;

    INSERT @Values …

    EXEC Test.spTVP @Values = @Values;


    Pass the data as a table valued parameter streaming1
    Pass the Data as a Table Valued Parameter Table Valued Parameters, XML, and MERGE Streaming

    • Pros

      • No need for staging the data in memory on the client side

  • Cons

    • Doesn’t stream all the way, stages the data on the server side

    • Requires a type to handle the streaming


  • A few m ore w ords on streaming
    A Few Table Valued Parameters, XML, and MERGEMore Words on Streaming

    • If you stream, how “far” do you stream?

      • N rows = N client server round trips & N proc. executions

        • Streams “all” the way to the destination table

      • Streaming TVP

        • Streams from client to just before the procedure begins execution, i.e., stages the data on the server side

      • The rest

        • Stages the data both on the client and server side

    • Any solution can implement streaming “manually”


    What happens and what a bout p erformance
    What Happens? And What Table Valued Parameters, XML, and MERGEAbout Performance?

    • Initial parsing of the data on the server

    • Querying the data

    • Insert the data into a table


    1 initial parsing on the server
    1. Table Valued Parameters, XML, and MERGEInitial Parsing on the Server


    1 initial parsing on the server1
    1. Table Valued Parameters, XML, and MERGEInitial Parsing on the Server


    2 querying the data
    2. Querying the Data Table Valued Parameters, XML, and MERGE


    2 querying the data1
    2. Querying the Data Table Valued Parameters, XML, and MERGE


    3 insert the arguments into a table
    3. Insert the Arguments into a Table Table Valued Parameters, XML, and MERGE


    3 insert the data into a table
    3. Insert the Data into a Table Table Valued Parameters, XML, and MERGE


    Agenda1
    Agenda Table Valued Parameters, XML, and MERGE

    • Passing a set of data to SQL Server

    • Adding MERGE to the equation


    Adding merge to the e quation
    Adding MERGE to the Table Valued Parameters, XML, and MERGEEquation

    • Also referred to as UPSERT

    • Allows for inserting, updating and deleting data in one statement

    • It is part of ANSI

      • …with one addition!


    Adding merge to the equation
    Adding MERGE to the Table Valued Parameters, XML, and MERGEEquation

    • Events

      • MATCHED

      • NOT MATCHED

      • NOT MATCHED BY SOURCE

    • Type of event

      • $action


    Adding merge to the equation1
    Adding MERGE to the Equation Table Valued Parameters, XML, and MERGE

    MERGE Test.Orders AS o

    USING @Values AS v

    ON v.OrderId = o.OrderId

    WHEN MATCHED THEN

    UPDATE SET

    CustomerId = v.CustomerId

    ,OrderDate = v.OrderDate

    ,DueDate = v.DueDate

    WHEN NOT MATCHED BY SOURCE THEN

    DELETE

    WHEN NOT MATCHED THEN

    INSERT (OrderId, CustomerId, OrderDate)

    VALUES(v.OrderId, v.CustomerId, v.OrderDate);


    question & answer Table Valued Parameters, XML, and MERGE


    Resources

    Required Slide Table Valued Parameters, XML, and MERGE

    Speakers,

    TechEd 2009 is not producing

    a DVD. Please announce that

    attendees can access session

    recordings at TechEd Online.

    Resources

    • www.microsoft.com/teched

      Sessions On-Demand & Community

    • www.microsoft.com/learning

    • Microsoft Certification & Training Resources

    • http://microsoft.com/technet

      • Resources for IT Professionals

    • http://microsoft.com/msdn

      Resources for Developers

    www.microsoft.com/learning

    Microsoft Certification and Training Resources


    Related content

    Required Slide Table Valued Parameters, XML, and MERGE

    Speakers,

    please list the Breakout Sessions, TLC Interactive Theaters and Labs that are related to your session.

    Related Content

    DAT313 Inside T-SQL: Enhancements, Techniques, Tips & Tricks

    DAT305 Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

    DAT04-INT Using the HIERARCHYID Datatype in Microsoft SQL Server 2008 to Maintain and Query Hierarchies

    300 - Advanced, Database Platform, Developer Tools, Languages and Frameworks, Hands-on Lab, Middle Tier Platform and Tools


    Sql server community resources

    Become a FREE PASS Member: Table Valued Parameters, XML, and MERGEwww.sqlpass.org/RegisterforSQLPASS.aspx

    Learn more about the PASS organization www.sqlpass.org/

    Additional Community Resources

    SQL Server Community Center www.microsoft.com/sqlserver/2008/en/us/community-center.aspx

    TechNet Community for IT Professionals

    http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx

    Developer Center

    http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx

    SQL Server 2008 Learning Portalhttp://www.microsoft.com/learning/sql/2008/default.mspx

    SQL Server Community Resources

    • Connect: Local Chapters, Special Interest Groups, Online Community

    • Share: PASSPort Social Networking, Community Connection Event

    • Learn: PASS Summit Annual Conference, Technical Articles, Webcasts

      • More about the PASS organization www.sqlpass.org/

    The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community.


    Additional resources
    Additional Resources Table Valued Parameters, XML, and MERGE

    • Team Forum: Speaker URL #2

    • Other: Speaker URL #3

    • External Resources

      • TVPs

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

      • MERGE

        • http://technet.microsoft.com/en-us/library/bb510625.aspx

    • SQL Server 2008 Business Value Calculator: www.moresqlserver.com


    Required Slide Table Valued Parameters, XML, and MERGE

    Complete an evaluation on CommNet and enter to win!


    Required Slide Table Valued Parameters, XML, and MERGE

    © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

    The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.


    ad