Optimizing microsoft sql server 2008 applications using table valued parameters xml and merge
This presentation is the property of its rightful owner.
Sponsored Links
1 / 34

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


  • 88 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

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

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

  • 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

  • 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

  • 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

  • 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

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

// 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 List

  • 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

// 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

  • 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

    // 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

    • 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 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  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 More 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 About 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. Initial Parsing on the Server


    1 initial parsing on the server1

    1. Initial Parsing on the Server


    2 querying the data

    2. Querying the Data


    2 querying the data1

    2. Querying the Data


    3 insert the arguments into a table

    3. Insert the Arguments into a Table


    3 insert the data into a table

    3. Insert the Data into a Table


    Agenda1

    Agenda

    • Passing a set of data to SQL Server

    • Adding MERGE to the equation


    Adding merge to the e quation

    Adding MERGE to the Equation

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

    • Events

      • MATCHED

      • NOT MATCHED

      • NOT MATCHED BY SOURCE

    • Type of event

      • $action


    Adding merge to the equation1

    Adding MERGE to the Equation

    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);


    Optimizing microsoft sql server 2008 applications using table valued parameters xml and merge

    question & answer


    Resources

    Required Slide

    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

    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: www.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

    • 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


    Optimizing microsoft sql server 2008 applications using table valued parameters xml and merge

    Required Slide

    Complete an evaluation on CommNet and enter to win!


    Optimizing microsoft sql server 2008 applications using table valued parameters xml and merge

    Required Slide

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


  • Login