Json usage and performance in sql server 2016

Json usage and performance in sql server 2016

• 1.
  JSON Usage and Performancein SQL Server 2016 NEO SQL Server User Group | Bert Wagner | April 4, 2017 1
• 3.
  Background ● BI developerat Progressive Insurance for 6+ years ● I love JSON - I use it in APIs, hardware projects, websites ● I also love SQL and relational database structures ● Slide, demos, code is available on bertwagner.com 3
• 4.
  Overview ● What isJSON? ● Why use JSON? ● When is it appropriate to store JSON in SQL? ● Usage examples: ○ ETL and reporting ○ Database object maintenance ○ Performance parsing 4
• 5.
  5 What does JSONlook like? { “Make” : “Volkswagen”, “Year” : 2003, “Model” : { “Base” : “Golf”, “Trim” : “GL” }, “Colors” : [“White”, “Pearl”, “Rust”], “PurchaseDate” : “2006-10-05T00:00:00.000Z” }
• 6.
  6 Why use JSON? EasyProcessing var car = { "Make" : "Volkswagen" }; console.log(car.Make); // Output: Volkswagen car.Year = 2003; console.log(car); // Output: { "Make" : "Volkswagen", "Year" : 2003" } Javascript:
• 7.
  7 Why use JSON? APIs
• 8.
  8 Why use JSON? StorageSize <Car> <Make>Volkswagen</Make> <Year>2003</Year> <Model> <Base>Golf</Base> <Trim>GL</Trim> </Model> <Colors> <Color>White</Color> <Color>Pearl</Color> <Color>Rust</Color> </Colors> <PurchaseDate> 2006-10-05 00:00:00.000 </PurcaseDate> </Car> { “Make” : “Volkswagen”, “Year” : 2003, “Model” : { “Base” : “Golf”, “Trim” : “GL” }, “Colors” : [“White”, “Pearl”, Rust”], “PurchaseDate” : “2006-10-05T00:00:00.000Z” } XML: 225 Characters JSON: 145 Characters
• 9.
  9 Appropriate Usage Staging Data ●Load data raw ● Validate ● Transform
• 10.
  10 Appropriate Usage Error Logging ErrorDateComponent Data 2016-03-17 21:23:39 GetInventory { "Make : "Volkswagen", "Year" : 2003} 2016-03-19 12:59:31 Login { "User" : "Bert", "Referrer" : "http://google.com", "AdditionalDetails" : "Invalid number of login attempts" }
• 11.
  11 Appropriate Usage Non-Analytical Data ●Sessions ● User preferences ● Non-frequently changing variables ○ Admin emails ○ Static dropdown menus
• 12.
  12 Inappropriate Usage High-Performance Requirements
• 13.
  13 Inappropriate Usage Validation/Integrity Requirements
• 14.
  14 Inappropriate Usage Being Lazy
• 15.
  Demos 1. ETL andreporting 2. Database object maintenance 3. Performance parsing 15
• 16.
  Performance Results 16 ● Indexeson computed columns are the solution ● Faster than C# libraries in some cases
• 17.
  Recap 17 ● Many good(and bad) uses for JSON in SQL exist ● JSON can be fully manipulated in SQL Server 2016 ● Performance is fast, especially with computed column indexes
• 18.
  Thank you! ● Twitter:@bertwagner ● Blog: https://bertwagner.com ● Email: bertwagner@bertwagner.com 18