Export to Excel - how long does it take

Question

Post reply

Export to Excel - how long does it take

Grinja

Hall of Fame

Points: 3590
More actions
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.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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)

Y.B. SSChampion Points: 11649 More actions · Answer 3

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

Grinja Hall of Fame Points: 3590 More actions · Answer 4

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 🙂