Post reply

High Report Export Volume

  • September 6, 2021 at 4:23 pm

    #3927248

    Hi There,

    I have a requirement to develop a solution to generate approximately 6,500 reports within a 2 hour processing window, and then export the reports in either PDF or Excel format to a secure ftp site.  The source is SQL and the report data is from stored procedures.  Currently this is being handled with Crystal reports, so I am hoping that SSRS might be a suitable alternative.

    Any thoughts or feedback would be gratefully received.

    Thanks in Advance

  • September 7, 2021 at 5:09 am

    #3927395

    If a TSV (Tab Separated Values) file or a CSV (Comma Separated Values) file that can be used by Excel is "good enough", you don't need Crystal Reports or SSRS, both of which seem like they'd be horribly slow to me.  A trick or two with BCP OUT would work a really fast treat.  That can be done either by a nice little batch file or a bit of (ugh!) Powershell or, like I do, use xp_CmdShell the right and secure way, the latter of which also makes a log of actions to a table for tracking purposes and easy task, as well.

    I don't know for sure because I don't use either Crystal Reports or SSRS but I don't believe they'll have the through rate for this.  With that, I'll also say I could be wrong about that and will have to defer to those that have more knowledge on those two subjects.

     

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

  • September 7, 2021 at 12:31 pm

    #3927473

    I agree with Jeff 🙂 However extracting the data and transferring to a secure ftp site at 1.1 sec per report is very optimistic.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 7, 2021 at 1:23 pm

    #3927507

    Thank you both for you input, it is much appreciated.

  • September 7, 2021 at 1:28 pm

    #3927508

    Sorry I should have added I thought this was a challenging ask, and wanted some ideas of what might be achievable.  I personally have never dealt with those report volumes in such a short processing window.  Technically by scaling out an SSRS server farm would that handle it all ?

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

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