Use JSON to Slash Your Database Performance.pdf

Led by Bill Reese

REST easy knowing your data has escaped the terror of being slashed up from unnecessary serialization. In this chillingly informative session, we'll unmask how to remove the overhead so your app can survive another day. Using Microsoft SQL Server, you will learn the keys to surviving the nightmare without killing performance. Bring your courage and your curiosity, but don’t worry—we’re not here to scare you; we’re here to help you conquer JSON and make your data structures unstoppable.

2. WELCOME
Into the Box 2025: The Future is Dynamic!

3. Code Necromancer — Raising Legacy Code from the Dead
Bill Reese
•25+ years of experience in full-stack application
and web development
•Using ColdFusion since version 4 and ColdBox
since version 2
•Based near Orlando Florida
•Married with three teenagers
•Love Movies, Music and Photography
•Excited to be creating BoxLang content for
CFCasts
•This is my first time speaking @ ITB
[email protected]
Senior Developer - Ortus Solutions
@Studio7718

4. Bill Reese - Into The Box 2025
No hockey mask needed!
Use JSON to Slash Your
Database Performance

5. Collection of ways to deliver data directly from the database using JSON
•We will be using a simple demo database of movies and TV shows with related tables for
actors, categories and various meta data.
•We will be getting movie and TV show information from the database using different methods
and delivering that data to and from REST API endpoints.
•JSON Data Types = string, number, boolean, array, object
1. Classic Method - Builds JSON document on application server using serializing and
deserializing functionality.
vs
2. JSON Method - Builds JSON document on database server using built-in JSON functionality.
What will we be doing?

6. Why would I want to use JSON in the Database?
Think differently about your data. Does this get updated frequently?
•Data type conversion issues
•Less database transactions, connections, queries
•Faster response time (almost all of the time)
•Less application server overhead
•Quick sub-object creation
•Utilize In-Memory storage of JSON documents
•Utilize Query Caching of JSON documents

7. SQL Functionality Covered
Some of the things we will be using in this demo
Microsoft SQL Server Functionality Covered
•FOR JSON PATH
•STRING_AGG
•STUFF & XML PATH
•JSON_QUERY
•OPENJSON
•OPENROWSET (Bulk)
•ISJSON
•STRING_ESCAPE
JSON Document Formatting Topics
•Using labels to build node paths
•Adding a root node(s)
•Data type conversion
•When to escape in strings
•Returning an array or single object

8. Data Type Conversion
SQL use the following rules when converting your data to JSON
SQL Server data type JSON data type
char, nchar, varchar, nvarchar string
int, bigint, float, decimal, numeric number
bit Boolean (true or false)
date, datetime, datetime2, time, datetimeoffset string
varbinary, binary, image, timestamp/rowversion BASE64-encoded string
geometry, geography, other CLR types Not supported. These types return an error.
In the SELECT statement, use CAST or CONVERT, or use a CLR property or
method, to convert the source data to a SQL Server data type that can be
converted successfully to a JSON type. For example, use STAsText() for
the geometry type, or use ToString() for any CLR type. The type of the
JSON output value is then derived from the return type of the conversion that
you apply in the SELECT statement.
uniqueidentifier, money string

9. Testing
We will compare both versions and see how they stack up against each other.
What are we testing?
•Request Response Time
•Database Response Time
•Database connections
•Memory Usage
Testing Output
•1000 Records

10. Getting up and running
Application Setup
Database Requirements
Microsoft SQL Server Version 2016+
COMPATIBILITY_LEVEL >= 130
CLOB enabled

BULK permission required for Insert and update method used
Demo App Install (CommandBox)
Application Setup
1.Setup Datasource
2.Create Endpoints
3.Create Models
4.Server start
Application Server Options
•BoxLang
•Lucee
•Adobe ColdFusion
NOTE: MySQL and PostgreSQL have similar
functionality

11. •Query output should be array format.
•Some documents may be too big and get split into separate rows in the
recordset. All rows of the array need to be combined to form the single JSON
String.
•JsonSqlRestHandler will handle the SQL output and prepare it for returning.
•returnStringJSON = Encapsulates data marshaling
•decodeSQLJSON = Combines recordset output into single string
•jsonMeta = Appends standard REST nodes if desired
* This functionality can be incorporated using DI or through inheritance
Different Output Methods
Handling the JSON string from the database and how it is returned

12. JsonSqlRestHandler - returnStringJSON

13. JsonSqlRestHandler - decodeSQLJSON

14. JsonSqlRestHandler - jsonMeta
Only Needed if trying to mimic
CB standard REST returns.

15. Output Code Demo

16. Read (Single Table)
Single Table
•FOR JSON PATH
{
"data": [
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"releaseYear": 2009,
"rating": "R",
"durationMinutes": 97,
"durationSeasons": 0,
"dateAdded": "2020-01-01"
},
{
"showId": 70140450,
"title": "The IT Crowd",
"type": "TV Show",
"releaseYear": 2013,
"rating": "TV-MA",
"durationMinutes": 0,
"durationSeasons": 5,
"dateAdded": "2017-08-01"
}
],
"error": false,
"pagination": {
"totalPages": 1,
"maxRows": 0,
"offset": 0,
"page": 1,
"totalRecords": 0
},
"messages": []
}

17. Read (Lists)
Related Data as Displayable List
Output directly to UI as a list of values with no
need to interact with or loop through the data.
•FOR JSON PATH
•STRING_AGG
•XML PATH
{
"data": [
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"releaseYear": 2009,
"rating": "R",
"durationMinutes": 97,
"durationSeasons": 0,
"dateAdded": "2020-01-01",
"categories": "Horror Movies",
"cast": "Aaron Yoo, Jonathan Sadowski, Derek Mears, Arlen Escarpeta, Danielle
Panabaker, Amanda Righetti, Ryan Hansen, Travis Van Winkle, Julianna Guill, Ben Feldman,
Jared Padalecki",
"directors": "Marcus Nispel"
},
{
"showId": 70140450,
"title": "The IT Crowd",
"type": "TV Show",
"releaseYear": 2013,
"rating": "TV-MA",
"durationMinutes": 0,
"durationSeasons": 5,
"dateAdded": "2017-08-01",
"categories": "British TV Shows, Classic & Cult TV, International TV Shows",
"cast": "Richard Ayoade, Katherine Parkinson, Matt Berry, Chris O'Dowd"
}
],
"error": false,
"pagination": {
"totalPages": 1,
"maxRows": 0,
"offset": 0,
"page": 1,
"totalRecords": 0
},
"messages": []
}

18. Read (Objects)
Related Data as Array of Object with Properties
Ability to loop though an array of objects
with properties.FOR JSON PATH
•FOR JSON PATH on Sub-queries
{
"data": [
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"releaseYear": 2009,
"rating": "R",
"durationMinutes": 97,
"durationSeasons": 0,
"dateAdded": "2020-01-01",
"categories": [
{
"category": "Horror Movies"
}
],
"cast": [
{
"name": "Aaron Yoo"
},
{
"name": "Amanda Righetti"
},
{
"name": "Arlen Escarpeta"
},
{
"name": "Ben Feldman"
},
{
"name": "Danielle Panabaker"
},
{
"name": "Derek Mears"
},
{
"name": "Jared Padalecki"
},
…

19. Read (Strings)
Related Data as Array of Strings
Ability to loop through an array of strings
or other simple data type.
•FOR JSON PATH
•STRING_AGG
•JSON_QUERY
{
"data": [
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"releaseYear": 2009,
"rating": "R",
"durationMinutes": 97,
"durationSeasons": 0,
"dateAdded": "2020-01-01",
"categories": [
"Horror Movies"
],
"cast": [
"Aaron Yoo",
"Amanda Righetti",
"Arlen Escarpeta",
"Ben Feldman",
"Danielle Panabaker",
"Derek Mears",
"Jared Padalecki",
"Jonathan Sadowski",
"Julianna Guill",
"Ryan Hansen",
"Travis Van Winkle"
],
"directors": [
"Marcus Nispel"
]
},
…

20. Read (Custom)
Custom formatted and Related Data as Array of Object with Properties
•FOR JSON PATH
•STRING_AGG
•JSON_QUERY
•ROOT Node
•PATH Label Nodes
•Data Type Conversion
{
"data": [
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"meta": {
"Release Year": 2009,
"rating": "R",
"duration": {
"minutes": 97,
"seasons": 0
},
"dateAdded": "01/01/2020"
},
"search": {
"categories": [
{
"categoryID": 1,
"Category": "Horror Movies",
"slug": "horror-movies"
}
]
},
"people": {
"cast": [
"Aaron Yoo",
"Amanda Righetti",
"Arlen Escarpeta",
"Ben Feldman",
"Danielle Panabaker",
"Derek Mears",
"Jared Padalecki",
"Jonathan Sadowski",
"Julianna Guill",
"Ryan Hansen",
"Travis Van Winkle"
],
"directors": [
"Marcus Nispel"
]
}
}
…

21. Read (Single Custom)
Single record custom formatted object
•FOR JSON PATH
•WITHOUT_ARRAY_WRAPPER
{
"showId": 70104894,
"title": "Friday the 13th",
"type": "Movie",
"meta": {
"Release Year": 2009,
"rating": "R",
"duration": {
"minutes": 97,
"seasons": 0
},
"dateAdded": "01/01/2020"
},
"search": {
"categories": [
{
"categoryID": 1,
"Category": "Horror Movies",
"slug": "horror-movies"
}
]
},
"people": {
"cast": [
"Aaron Yoo",
"Amanda Righetti",
"Arlen Escarpeta",
"Ben Feldman",
"Danielle Panabaker",
"Derek Mears",
"Jared Padalecki",
"Jonathan Sadowski",
"Julianna Guill",
"Ryan Hansen",
"Travis Van Winkle"
],
"directors": [
"Marcus Nispel"
]
}
}

22. Read (Flat File)
Read a JSON text file from SQL into a query
•OPENROWSET
•BULK Insert

23. Read (JSON Variable)
Pass a JSON string into a query
This will be used in the next examples.
•OPENJSON
[ { show }, { show } ]

24. Writing to the database using JSON
•OPENJSON
•BULK Insert
[ { show }, { show } ]
•Create Record(s) Example
Insert one or more records from a JSON string into a table
•Update Record(s) Example
Update one or more records in the database from JSON string nodes
•Delete Record(s) Example
Delete one or more records using nodes in a JSON string

25. CRUD Code Demos

26. Putting It All Together
Lets use everything we learned and see what we can do!
•Get custom formatted JSON for all records
•Insert into memory optimized SQL table
•Return all rows from memory optimized table
•Query cache results
How long will the query take to get the JSON back?

27. Cache Code Demo

28. Things to look out for
Some things that may trip you up
•Data Type conversion failures - i.e. Use a string for a number. Utilize CAST or CONVERT.
•An error occurred during the current command (Done status 0). JSON text is not properly
formatted. Unexpected character ‘P’
Find the column(s) and wrap it with STRING_ESCAPE
•NULLS - Node will not show, if expected use ISNULL
•Case Sensitivity - Write the way it should be returned, watch table alias with auto
•Keep related nodes together - Data will disappear
•Document Size vs Character Limits - JSON_VALUE will truncate @ 4000 characters, use
JSONQUERY
•Bad / Duplicate Labels - Garbage in garbage out

29. Qb Example
Lets use qb to get similar results
•Classic QB
•JSON QB
•Using Custom Grammar File
•Custom Edits to handle JSON functionality

30. QB Code Demo

31. Q & A

32. Thank You
www.linkedin.com/in/studio7718/
GitHub.com/Studio7718/SQLJSONDEMO/