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

2. Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE Tobias Ternstrom Program Mgr., SQL Server Engine Microsoft Corporation DAT 320

3. Agenda • Passing a set of data to SQL Server • Adding MERGE to the equation

4. Passing a Set of Data to SQL Server • N rows = N executed statements • N rows = 1 executed statement

5. Passing a Set of Data to SQL Server • N rows = N executed statements • One client server roundtrip per execution • All executions in one batch

6. 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)

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

8. 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 = '…|…|… …|…|… …|…|…';

9. 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”

10. 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…

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

12. 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;

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

14. 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;

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

16. 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”

17. What Happens? And What About Performance? • Initial parsing of the data on the server • Querying the data • Insert the data into a table

18. 1. Initial Parsing on the Server

19. 1. Initial Parsing on the Server

20. 2. Querying the Data

21. 2. Querying the Data

22. 3. Insert the Arguments into a Table

23. 3. Insert the Data into a Table

24. Agenda • Passing a set of data to SQL Server • Adding MERGE to the equation

25. 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!

26. Adding MERGE to the Equation • Events • MATCHED • NOT MATCHED • NOT MATCHED BY SOURCE • Type of event • $action

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

28. question & answer

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

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

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

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

33. Required Slide Complete an evaluation on CommNet and enter to win!

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