JSON performance

  • Evgeny Garaev

    SSCertifiable

    Points: 6170

    Comments posted to this topic are about the item JSON performance

  • HappyGeek

    SSCoach

    Points: 18662

    Good question thanks Evgeny

    ...

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71364

    Nice question, thanks Evgeny

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

  • t.ovod-everett

    Ten Centuries

    Points: 1292

    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: 994558

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Thomas Franz

    Hall of Fame

    Points: 3544

    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: 286958

    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.

  • Mighty

    SSCrazy Eights

    Points: 8435

    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

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

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