Post reply

Are there any limits to Pivoting

  • January 5, 2017 at 7:01 am

    #325038

    We have a database that has been designed to service the application - currently there is no real reporting database, reporting is done off a replicated copy of the live DB. (This is changing and I will be setting up a reporting database).

    For some data extracts it is necessary to pivot the data into a usable format. One particular extract that has been working for a year is suddenly falling over due to it filling the TempDB. For a little size context, the total DB is 80Gb, the tempDB has 500Gb available to it.

    By breaking down the query I noticed that we are now pivoting, partitioning and ordering over 20 million rows of data. I`m wondering if there is a hard limit to what SQL can actually pivot and whether I need to look at a report redesign.

    Anyone come across anything similar ?

    Thanks

  • January 5, 2017 at 7:27 am

    #1920872

    simonsmithbsa (1/5/2017)

    We have a database that has been designed to service the application - currently there is no real reporting database, reporting is done off a replicated copy of the live DB. (This is changing and I will be setting up a reporting database).

    For some data extracts it is necessary to pivot the data into a usable format. One particular extract that has been working for a year is suddenly falling over due to it filling the TempDB. For a little size context, the total DB is 80Gb, the tempDB has 500Gb available to it.

    By breaking down the query I noticed that we are now pivoting, partitioning and ordering over 20 million rows of data. I`m wondering if there is a hard limit to what SQL can actually pivot and whether I need to look at a report redesign.

    Anyone come across anything similar ?

    Well, there was this discussion a few years ago... The question is, in this case, how many columns are you pivoting into?

    There are alternative query types you can use instead of pivoting which may give better performance, for example Jeff Moden's articles on Cross Tabs and Pivots[/url]

    Without knowing what you're doing, it's hard to offer any specific advice.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • January 5, 2017 at 7:38 am

    #1920875

    Doing some breakdown tests now. There is nothing wrong with the underlying data. It works fine if I limit the result set to x million rows. I`m guessing that we are running out of space on the sorting and grouping and there are limits on those.

    I can feel a rewrite coming on.

  • January 5, 2017 at 7:40 am

    #1920876

    I'll also say that none of this should require such a load on TempDB. It's a very strong indicator that there's at least one (and it only takes 1 with so many rows) accidental Cartesian product (think "cross join" or "triangular" join) in the form of a many-to-many join in the code somewhere. Many times, people will try to overcome this with a GROUP BY (required for CrossTabs/Pivots) or a DISTINCT instead of actually resolving the issue. There's also a particularly good chance that someone tried to write this as a single query (which does NOT necessarily mean that it's proper set based code).

    My recommendation would be that the code has reached a "tipping point" due to increase in scale and needs to be researched and rewritten using techniques such as "Divide'n'Conquer" and "Pre-Aggregation" (which is discussed in the article that Thomas Rushton provided a link for).

    --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)

Viewing 4 posts - 1 through 3 (of 3 total)

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