Post reply

Export to Excel - how long does it take

  • November 18, 2022 at 5:11 pm

    #4114956

    Hello,

    I have not used SSRS in a number of years so hoping for some anecdotal feedback.

    Recently I have joined project where it takes ~ 5 min to export 50k rows to excel.

    Export to csv is within 40 seconds typically.

    This is on a plain tabular grid - no fancy formatting.

    Care to share what your experience is when extracting data in the 50 - 100K range to excel?

    (SSRS 2017 | VM 2 Logical Cpu @2.2 GHZ , 32GB ram - Cpu utilisation is 50% on each core while exporting | DB is on separate hardware)

    Thanks for taking time to read this post!

    G

    • This topic was modified 2 years ago by  Grinja.

  • November 19, 2022 at 6:10 pm

    #4115328

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 20, 2022 at 5:29 pm

    #4115594

    To be honest, I don't use either SSRS or SSIS for such things.  I'll generally create a stored procedure to execute and create a "refreshable" spreadsheet that will run the stored procedure and return the data.  It will run that proc depending on their sign in for the domain.  If they don't have privs, it'll fail.

    I'll also say, regardless of method, that returning 50-100 thousand rows to a spreadsheet doesn't sound like the best thing in the world to do.  It would be better to find out what they intend to do with the data as a summary and write a proc to return just that summary.

    To wit, I'll remind folks...

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

  • November 21, 2022 at 3:48 pm

    #4115988

    OMG Jeff, I almost spit out my drink after seeing the "Dashboard" image.  The truth is some will want it in Excel no matter how great the Dashboard is.  It's not a technological problem but rather a habit and a little fear.  All "they" see is a new way for you to deliver the data to a spreadsheet rather than INSTEAD of a spreadsheet.  Can't tell you how many managers with access to said dashboards don't ever open them up.  They get someone else to export it to Excel and read it that way.  Why would you not want to look it up anytime you want, refreshed and everything!!

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • November 21, 2022 at 8:06 pm

    #4116142

    Those are great points both, and the humour is spot on haha.

    I think this is what they refer to as "self-service" BI - give us all the data and we will build our own dashboards ... in Excel 🙂

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

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