#Sql server json query speed code#

#Sql server json query speed code#

This is not ideal of course, but unfortunately this is currently how the code works.

This means if you select js::a and js::b, even if they are both on JSON column js, they are internally considered as two different “fake JSON columns” for the purpose of MemSQL query execution. relational database that supports both SQL (relational) and JSON (non-relational) querying. Each JSON key you asked for is considered as a special “fake JSON column” in your table. PostgreSQL Vs SQL Server: Programming Language Support. However, things are a little different for JSON key extraction. Even if a and b are used multiple times in your projections, they are only extracted from disk once. We extract the data from column a and b, then compute all your projections. For example, if your table has integer column a,b,c and you do select a+1, a+b, b+1, b+2, then column a and b are needed, but c is not. This is kind of tech detail, but in case you are interested: we first compute a list of table columns needed to compute all your projections. Of course, reading a JSON string as a table is rather easier. On regular columns, the same query would take 10-20ms, even if there are many selected. SELECT dbo.ToJSON(MyHierarchy) And it won’t surprise you that the string is more or less identical to the one we got in PowerShell. The question is, why is there no caching and why is the same query taking the same amount time of 800ms one after another. The second run should not require any hard disk read, as the data should have been in Linux page cache. If your query’s working set fits into memory, a easy way to get rid of disk-related complication is to run the identical query twice. If you could confirm the CPU usage by logging into the leaf directly we can investigate further by collecting a flamegraph.

You need to collect a flamegraph to know exactly what’s going on.Ħ% CPU utilization sounds suspicious, but I wouldn’t 100% trust the studio, especially for such short-running query (it runs for only 0.7s according to what you said). I am not sure where the 650-700ms is spent. So I would expect run time grow linearly with the number of projections you have in your query. 4096 rows in one segment) by block, and column by column in each block. The thread is responsible for extracting all the requested data in that partition. The query execution is single-threaded (per partition), same as the other data types. We use a 3rd party library called parquet to store JSON column and extract json keys.