quassnoi/nesql-2023-05

What is it?

This is support code for the lightning talk I gave for New England SQL Server group in May, 2023.

It compares performance of JSON and Table Variables as a means to pass tabular data to SQL Server stored procedures.

Prerequisites

• VSCode
  • SQL Database Projects extension
• Docker
• .NET 7.0 SDK

Setup

Install the database in a Docker container

1. In the VSCode side menu, locate Database Projects
2. Right-click on the project sql, select "Publish"
3. Select "Publish to new SQL server local development container"
4. Select the default port (1433)
5. For the admin password, select Password123. If you choose another password, you will have to change it in the appsettings.json as well.
6. Keep selecting default values
7. The publish process will start and complete in several seconds.
8. VSCode should show a toast message saying "Database project published successfully"

Install the dotnet-script framework

cd src
dotnet tool restore

Run the test scripts

Inside the src folder:

# Measure Table Variable performance
./runTv.sh

# Measure JSON performance
./runJson.sh

1. The test scripts start 8 parallel worker processes.
2. Each worker process runs the stored procedures sequentially in a loop. At any given point in time 8 procedures are running concurrently (less negligible time spent on running C# code between invocations for populating parameters, parsing results and measuring time).
3. Every 20 invocations of the stored procedure, a worker prints hash character (#) on the screen.
4. The test scripts run until stopped with a Ctrl-C.
5. If it doesn't work, check appsettings.json. It assumes that you followed the steps in the section "Install the database" above.

OPENJSON as a streaming function

Plain query

Open PlainQuery.sql and run it.

It takes a JSON array with two objects in it and convert it to a recordset with two records. Exactly how it should be.

Zalgo query

Open ZalgoQuery.sql and run it.

Oh no! This is not a valid JSON.

It starts as an array of two objects, but then it looks like Zalgo, the chthonic extradimensional being, is trying to creep into our world through a database query.

Despite the fact that it is not a valid JSON and doesn't parse, the query runs and returns the same two-record result as the previous one.

This is because OPENJSON is working with the JSON data in a streaming fashion. The SELECT clause of the Zalgo query has the TOP 2 modifier, which means that OPENJSON will only need to retrieve two records from the underlying JSON.

Once it has retrieved the two records, it stops reading and discards Zalgo-infected portion of the text. Our world is safe.

Have fun!