JSON performance

Question

Post reply

JSON performance

Evgeny Garaev

SSCertifiable

Points: 7428
More actions
February 24, 2018 at 11:17 am

#343732

Comments posted to this topic are about the item JSON performance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

February 25, 2018 at 12:03 am

#1981044

Good question thanks Evgeny

...

Stewart "Arturius" Campbell SSC Guru Points: 72622 More actions · Answer 2

Nice question, thanks Evgeny

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

t.ovod-everett Default port Points: 1487 More actions · Answer 3

I think the assertion that "standard indexes" will improve JSON performance is misleading. That is only the case if "standard indexes" includes indexing on computed columns. Sticking a normal index on the JSON column will only improve performance to the extent that a covering index improves performance because it reduces the number of pages relative to the clustered index because there are missing columns. To actually get index-like behavior (the ability to retrieve a subset of records based on their position within the index), one must create a computed column and then index that computed column. And that's exactly what the link https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data indicates. But I wouldn't usually refer to an index on a computed column as a "standard index".

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

Heh... "JSON performance"... Isn't that what they call an "oxymoron"? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Franz Hall of Fame Points: 3892 More actions · Answer 5

Another problem: JSON could be stored in a (N)VARCHAR(MAX) field which could not be added to an index (only included, but this would only allow full index scans, even if you use a sophisticated LIKE) ...

God is real, unless declared integer.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Jeff Moden - Monday, February 26, 2018 11:23 AM
Heh... "JSON performance"... Isn't that what they call an "oxymoron"? 😉

Yeah, I think it is. I thought a couple of the answers available were good, though. They made me turn my head and wonder if they're the "next big shiny thing" on Microsoft's hit list.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Mighty SSCrazy Eights Points: 9063 More actions · Answer 7

t.ovod-everett - Monday, February 26, 2018 11:00 AM
I think the assertion that "standard indexes" will improve JSON performance is misleading. That is only the case if "standard indexes" includes indexing on computed columns. Sticking a normal index on the JSON column will only improve performance to the extent that a covering index improves performance because it reduces the number of pages relative to the clustered index because there are missing columns. To actually get index-like behavior (the ability to retrieve a subset of records based on their position within the index), one must create a computed column and then index that computed column. And that's exactly what the link https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data indicates. But I wouldn't usually refer to an index on a computed column as a "standard index".

+1