Performance issue with large number of rows

  • okbangas

    SSChampion

    Points: 11773

    Hi folks.

    A customer of mine has requested a report which possibly can return several thousand rows. The report shall NOT be paged, as it is much faster for the end users to scroll down a large report than guessing how many pages there is, and what page they're supposed to go to. This report is now rendering just fine, and the report server is only using a few seconds to process the report. IE however, is using more than 3 minutes of CPU time to execute the JavaScript rendering the table. Does anyone know any trick, hack or whatever to improve the performance of the HTML report?



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • okbangas

    SSChampion

    Points: 11773

    I have now digged a bit more into it, and I fear that there is nothing we can do about this, although I sincerely hope that I am wrong. It appears that the entire report is created with JavaScript, which I do find a bad idea in the first place. What's even worse, is that all sizing is overridden by JavaScripts, which is used to calculate the sizes in pixels, no matter what size you originially specified in your report. What's even worse, for every conversion to pixels, a new div is created with a width of 72pt, which is used to calculate dpi, then this div is deleted. So, for my humble table with some 3000 rows of 20 columns + paragraphs, links and so on, Reporting Services is creating a temporary div in the object model for the left, top, right and bottom padding and border of each and every element, at least once. A simple calculation: 3000*20*8 = close to half a million DIVs are created to perform half a million calculations of dpi, that I have discovered so far. Honestly, this should have been done once for the entire report.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Jeff Moden

    SSC Guru

    Points: 997205

    Now that you've found out what the massive overhead is, can't you just bypass it and build a report of your own?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • okbangas

    SSChampion

    Points: 11773

    Jeff Moden (3/2/2012)


    Now that you've found out what the massive overhead is, can't you just bypass it and build a report of your own?

    You mean create a web solution for this myself instead of using SSRS? Actually, it is tempting. And it would perform WAY better than SSRS. Presenting a table should not be that difficult either.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Jeff Moden

    SSC Guru

    Points: 997205

    okbangas (3/2/2012)


    Jeff Moden (3/2/2012)


    Now that you've found out what the massive overhead is, can't you just bypass it and build a report of your own?

    You mean create a web solution for this myself instead of using SSRS? Actually, it is tempting. And it would perform WAY better than SSRS. Presenting a table should not be that difficult either.

    Could be. Since I don't even know how to spell "web app", I'd do it all (and very quickly) in a stored proc. I know the word "all" here will rile some folks up because I do mean to include the simple HTML formatting in the word "all". What the heck... it's no worse than anything I've seen anyone do with XML and, done correctly, can render a report in a heartbeat.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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