7/20/2023 0 Comments Sql server json query performanceIf you find out that your queries frequently search documents by some property (for example, a severity property in a JSON document), you can add a classic NONCLUSTERED index on the property to speed up the queries. This ability to use rich T-SQL query syntax is the key difference between SQL Server and SQL Database and classic NoSQL databases - in Transact-SQL you probably have any function that you need to process JSON data. You can extract values from a JSON document with the JSON_VALUE function and use it in the query like any other value. SQL Server and SQL Database don't introduce any constraints in the queries that you can use to analyze JSON documents. It's a powerful advantage that you can use any T-SQL function and query clause to query JSON documents. ORDER BY AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) DESC HAVING AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) > 100 WHERE CAST( JSON_VALUE(log,'$.date') as datetime) > BY JSON_VALUE(log, '$.severity') For example: SELECT TOP 100 JSON_VALUE(log, '$.severity'), AVG( CAST( JSON_VALUE(log,'$.duration') as float)) When you store your JSON documents in the table, you can use standard Transact-SQL language to query the documents. Without the constraint, the table is optimized for inserts, because any JSON document is added directly to the column without any processing. For example: ALTER TABLE WebSite.LogsĪDD CONSTRAINT Įvery time someone inserts or updates a document in the table, this constraint verifies that the JSON document is properly formatted. If you want to be sure that valid JSON is saved in the log column, you can add a CHECK constraint on the column. The sample table created in the preceding example assumes that valid JSON documents are stored in the log column. If you're sure that your JSON documents aren't greater than 8 KB, however, we recommend that you use NVARCHAR(4000) instead of NVARCHAR(max) for performance reasons. The nvarchar(max) data type lets you store JSON documents that are up to 2 GB in size. This structure is a good choice for the classic NoSQL scenarios where you want to retrieve a document by ID or update a stored document by ID. The primary key _id is an auto-incrementing value that provides a unique identifier for every document and enables fast lookups. This structure is equivalent to the collections that you can find in classic document databases. The simplest way to store JSON documents in SQL Server or SQL Database is to create a two-column table that contains the ID of the document and the content of the document. This approach increases the load time because JSON parsing is done during load however, queries match the performance of classic queries on the relational data. Fragments from the input JSON documents can be stored in the SQL data type columns or in NVARCHAR columns containing JSON sub-elements. Relational storage - JSON documents can be parsed while they are inserted in the table using OPENJSON, JSON_VALUE or JSON_QUERY functions.This approach might introduce an additional performance penalty on query/analysis time if indexing on JSON values is not performed, because the raw JSON documents must be parsed while the queries are running. This is the best way for quick data load and ingestion because the loading speed matches the loading speed of string columns. LOB storage - JSON documents can be stored as-is in NVARCHAR columns.The first storage design decision is how to store JSON documents in the tables. This article describes the options for storing JSON documents in SQL Server or SQL Database. You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database. SQL Server and Azure SQL Database have native JSON functions that enable you to parse JSON documents using standard SQL language.
0 Comments
Leave a Reply. |