1 / 48

SQL Server 2008: Powering MSDN

TL42. SQL Server 2008: Powering MSDN. Mark Johnston Development Lead - MSDN Microsoft Corporation johnston.mark@microsoft.com. Agenda SQL Server 2008: Powering MSDN. Introduction Challenge Solution Additional Uses Of The Solution Questions?. Introduction Within MSDN. MSDN.

kalani
Download Presentation

SQL Server 2008: Powering MSDN

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TL42 SQL Server 2008: Powering MSDN Mark Johnston Development Lead - MSDN Microsoft Corporationjohnston.mark@microsoft.com

  2. AgendaSQL Server 2008: Powering MSDN • Introduction • Challenge • Solution • Additional Uses Of The Solution • Questions?

  3. IntroductionWithin MSDN MSDN This talk is only about this part of MSDN Web Sites Subscriptions MSDN Magazine Offline Help

  4. IntroductionLogical Overview Content Providers Online Services MSDN • Visual Studio • SQL Server • Exchange • Windows • Office • KB Articles • … • Web Sites • Web Services • RSS Feeds • …

  5. IntroductionMSDN Topology Overview SQL Servers IIS/Web Transactional Replication Content Providers Datacenter 1 Visual Studio SQL Server Exchange Office Windows KB Articles … Datacenter 2 All Content is Stored in SQL Server

  6. IntroductionContent Submission

  7. IntroductionContent By The Numbers • Count of content stored in SQL Server: 12 million • Content providers peak submission of content per day: 1.25 million • Most rows replicated in one day: 30 million • On average each content is normalized into 40 rows per content • Average web server hits per day: 40 million

  8. Agenda • Introduction • Challenge • Solution • Additional Uses Of The Solution • Questions?

  9. ChallengeMinimize The Number of Rows That Are Replicated • Minimize the number of rows replicated when content is submitted to MSDN • Note that an update of a row where no values are changed will replicate the row

  10. Agenda • Introduction • Challenge • Solution • Additional Uses Of The Solution • Questions?

  11. SolutionAgenda • Describe two new features of SQL Server 2008 • MERGE statement • Table-Valued Parameters • Combine those two new features to implement the solution

  12. MERGE StatementWhat Is It? • The MERGE statement is a new feature of SQL Server 2008 • It is a combination of INSERT, UPDATE, and/or DELETE in one statement So, you don’t have to write code like if not exists (select * from tbl where name = @name) insert tbl (…) select … else update tbl set … where name = @name

  13. MERGE StatementCoding Example • MERGE employee as target • USINGemployee_changesassrc • ONsrc.loginid = target.loginid • WHENMATCHEDTHEN/* UPDATE */ • UPDATESET first = src.first • ,last = src.last • WHENNOT MATCHEDTHEN/* INSERT */ • INSERT (loginid,first,last) • VALUES(src.loginid,src.first,src.last) • WHENNOTMATCHEDBYSOURCETHEN • DELETE; /* DELETE */

  14. MERGE StatementWhy use MERGE? • Performance • The row(s) are read only once • MERGE is atomic • Help avoid race conditions

  15. MERGEDelta Processing Employee_changes “source” table Employee “Target“ Table Applied to This process is known as Delta Processing Employee “Target” result after MERGE UPDATE untouched INSERT DELETE

  16. demo MERGE Mark Johnston Development Lead - MSDN Microsoft Corporation johnston.mark@microsoft.com

  17. MERGE Phone Number DemoProblem Definition • Temp table #t holds a list of phone numbers for a given employee • Replace the employee’s phone numbers with the phone numbers in the temp table Create table #t (loginidvarchar(15) not null ,Type char(4) not null ,phoneNumvarchar(15) not null )

  18. demo MERGE Phone Numbers Mark Johnston Development Lead - MSDN Microsoft Corporation johnston.mark@microsoft.com

  19. MERGE Phone Numbers DEMO Problem #1: Error: target row matches more than one source row Source Target Need to fix the ON clause of the MERGE statement

  20. MERGE Phone Numbers DEMO Problem #2: Inadvertent rows are being deleted Source Target UPDATE untouched INSERT DELETE DELETE Need to properly define the scope of the target

  21. MERGE Phone Demo Summarized • ;WITH target • AS (SELECT ep.* FROMEmployeePhoneASep • JOIN (SELECT distinct loginId from #t) t • ON t.loginId = ep.loginId) • MERGE target • USING (SELECT * FROM #t) AS SRC • ONSRC.loginid = target.loginid • AND SRC.Type = target.type • WHENMATCHED • AND (target.phoneNum <> SRC.phoneNum) • THENUPDATESETtarget.phoneNum=SRC.phoneNum • WHENNOTMATCHEDTHEN • INSERT (loginid, type, phoneNum) • VALUES (SRC.loginid, SRC.type, SRC.phoneNum) • WHENNOTMATCHEDBYSOURCETHENDELETE; Untouched

  22. MERGE Statement • VENN Diagrams Source Target Insert Delete Update Subset Subset Insert Update Delete

  23. MERGE Statement SummaryKey Points and Takeaways • Define the scope of the source • Define the scope of the target to match the scope of the source • Define the ON clause between source and target • Add conditions to the “UPDATE” section to handle the “untouched” • MERGE statement can contain any combination of INSERT, UPDATE, and DELETE • Under the covers, MERGE uses FULL JOINS

  24. Table-Valued ParametersDefinition • Table-Valued Parameters (TVP) are a new parameter type in SQL Server 2008 that enable multiple rows to be sent to a routine • TVP’s are like parameter arrays

  25. Table-Valued ParametersBenefits of Table-Valued Parameters • Performance • XML is a bloated transport • Delimited lists have to be parsed • Calling a stored procedure many times is inefficient • Strongly typed • A given routine can accept zero or more TVP’s • Provides a simple programming model

  26. demo TVP and C# Mark Johnston Development Lead - MSDN Microsoft Corporation johnston.mark@microsoft.com

  27. Table-Valued Parameters What TVP Generated Code Looks Like • declare @p1 dbo.EmployeeTableType • insertinto @p1 values • (N’JohnA’,N’John’, N’Adams’) • declare @p2 dbo.EmployeePhoneTableType • insertinto @p2 values (N’JohnA’,N’cell’, N’1-555-456-7890’) • insertinto @p2 values (N’JohnA’,N’home’, N’1-555-425-8080’) • insertinto @p2 values (N’JohnA’,N’work’, N’1-555-112-1234’) • execdbo.mergeEmployeeAndPhoneWithTVP @employee=@p1,@employeePhone=@p2

  28. Table-Valued ParametersKey Points and Takeaways • TVP’s and MERGE work well together • TVP’s are easy to use from C# • Any object derived from DbDataReader can stream rows of data to a TVP

  29. SolutionOriginal MSDN Content Submission • execcontentInsertUpdate … • /*if update content then delete rows in • related tables associated with content */ • execcontentPrimaryDocInsert … • execcontentPrimaryDocInsert … • … • execcontentImageInsert … • execcontentImageInsert … • … • execcontentMetaDataInsert … • execcontentMetaDataInsert … • … • /* there are 10 content related tables */

  30. SolutionNew Content Submission With MERGE and TVP • execcontentMerge • @contentInfo/* contentTableType */ • ,@primaryDoc/* primaryDocTableType */ • ,@images /* imageTableType */ • ,@metaData/* metaDataTableType */ • … • /* All data related to the content is passed into one stored procedure */

  31. SolutionSummary • TVP’s and MERGE look great separately • Together they are intriguing…

  32. Agenda • Introduction • Challenge • Solution • Additional Uses Of The Solution • Questions?

  33. Additional Uses Of The Solution • Data Migration or Transfer Tool • Data Diff Tool • New Paradigm? • …

  34. demo Transfer Tool Mark Johnston Development Lead - MSDN Microsoft Corporation johnston.mark@microsoft.com

  35. Additional Uses Of The SolutionData Diff Tool • Data Diff Tool could be used to compare data across servers/databases • For instance, check if a specific employee on one database has the same data in another database • The implementation would be like the transfer tool but FULL JOINS would be used rather than MERGE

  36. Additional Uses Of The SolutionNew Paradigm? Traditional Method for App Design • Each table has Insert, Update, and Delete stored procedures EmployeeInsert EmployeePhoneInsert Employee EmployeePhone EmployeeUpdate EmployeeDelete EmployeePhoneUpdate EmployeePhoneDelete

  37. Additional Uses Of The SolutionNew Paradigm? • Each table has one MERGE stored procedure that takes a table-valued parameter EmployeeMerge EmployeePhoneMerge Employee EmployeePhone

  38. Additional Uses Of The SolutionNew Paradigm? • Each logical entity has a stored procedure doing MERGE on all the related tables EmployeeMerge Employee EmployeePhone EmployeeAddress

  39. Additional Uses Of The SolutionNew Paradigm? MSDN Implementation contentMerge content contentPrimaryDoc contentImage contentMetaData execcontentMerge @contentInfo/* contentTableType */ ,@primaryDoc/* primaryDocTableType */ ,@images /* imageTableType */ ,@metaData/* metaDataTableType */ …

  40. Additional Uses Of The SolutionSummary One final thing… I provided ideas and possible solutions but the bottom line is… DESIGN TO YOUR NEEDS/REQUIREMENTS Consider the concepts a tool and appropriately use the tool for your needs

  41. Resources MERGEMERGE (Transact-SQL)http://msdn.microsoft.com/library/bb510625.aspxOptimizing MERGE Statement Performance http://msdn.microsoft.com/library/cc879317.aspxInserting, Updating, and Deleting Data with MERGE http://msdn.microsoft.com/library/bb522522.aspx Table-Valued ParametersTable-Valued Parameters (Database Engine)http://msdn.microsoft.com/library/bb510489.aspxTable-Valued Parameters in SQL Server 2008 (ADO.NET)http://msdn.microsoft.com/library/bb675163.aspx

  42. Questions? Mark Johnston Development Lead – MSDN Microsoft Corporation johnston.mark@microsoft.com

  43. Evals & Recordings Please fill out your evaluation for this session at: This session will be available as a recording at: www.microsoftpdc.com

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

  45. Appendix Mark Johnston Development Lead – MSDN Microsoft Corporation johnston.mark@microsoft.com

  46. MERGECan you spot the race condition? • IF exists (select * from Product whereproductNumber = ‘AR-5381’) • BEGIN • update product • set … • whereproductNumber = ‘AR-5381’ • END • ELSE • BEGIN • INSERT product (…) • SELECT ‘AR-5381’, … • END Race condition

  47. MERGEWays to avoid the race condition To avoid the race condition, the statement needs to be atomic with either • Use MERGE statement • Code up your own atomic statements INSERT Product (…) SELECT … WHERE NOT EXISTS (SELECT * FROM product WHERE partNumber = @partNumber ) UPDATE product SET …

More Related