Post reply

Monthly Report Query

  • February 15, 2012 at 6:16 am

    #391565

    Dear All,

    We found for monthly report that if we choose different result showing format, the cost time of the query will different as well, current the total records is 5,999,557.

    So we use excel to import the data one time for data analysis, so as usual we will query the result in text format and during this process we will separate the data into different text files to get the data for excel import.

    Here is time that query result showed in different format:

    1. Query Monthly report (A1) with result in grid, it will take 4minutes and 9 seconds.

    2. Query Monthly report（A1）with result in text, this time it has taken us 21 hours,20

    minutes and 38 seconds.(That time one day end is running on another database on that

    machine.)

    We need this report every month, what is your suggestion for us for future report?

    Thanks

    Adeel Imtiaz

  • February 15, 2012 at 7:25 am

    #1447314

    I have read your question a few times, but I still have no idea what you're asking.

    What tools are you using? What are you exactly trying to accomplish?

    edit: fixed typo

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 15, 2012 at 10:12 am

    #1447408

    Are you saying that you are running this "report" in SSMS? Or are you exporting to a file from SSRS?

    Jared
    CE - Microsoft

  • February 15, 2012 at 12:42 pm

    #1447504

    adeel.imtiaz (2/15/2012)

    1. Query Monthly report (A1)[font="Arial Black"] with result in grid[/font], it will take 4minutes and 9 seconds.

    2. Query Monthly report(A1)[font="Arial Black"]with result in text[/font], this time it has taken us 21 hours,20

    minutes and 38 seconds.(That time one day end is running on another database on that

    machine.)

    We need this report every month, what is your suggestion for us for future report?

    Rewrite it so it returns in 5 seconds in either mode? 😉

    Are you really serious? You're generating a report with nearly 6 million lines of output? Who's going to read THAT?

    --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)
    Intro to Tally Tables and Functions

  • February 15, 2012 at 12:49 pm

    #1447507

    Jeff Moden (2/15/2012)

    adeel.imtiaz (2/15/2012)

    1. Query Monthly report (A1)[font="Arial Black"] with result in grid[/font], it will take 4minutes and 9 seconds.

    2. Query Monthly report(A1)[font="Arial Black"]with result in text[/font], this time it has taken us 21 hours,20

    minutes and 38 seconds.(That time one day end is running on another database on that

    machine.)

    We need this report every month, what is your suggestion for us for future report?

    Rewrite it so it returns in 5 seconds in either mode? 😉

    Are you really serious? You're generating a report with nearly 6 million lines of output? Who's going to read THAT?

    As always... Good points Jeff 🙂 Ignoring practicality... Something else is happening though if the result times are THAT different when rendering from grid to text. Right? I would think the query execution time is what it is, and the redering results is really what's causing an issue? In fact, rendering the results to text should always be a bit faster?

    Jared
    CE - Microsoft

  • February 15, 2012 at 2:59 pm

    #1447563

    Jeff Moden (2/15/2012)

    Are you really serious? You're generating a report with nearly 6 million lines of output? Who's going to read THAT?

    Usually the business imports the results into an Excel sheet and do some filtering there.

    Because you know, TSQL doesn't have WHERE clauses and parameters...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 15, 2012 at 4:29 pm

    #1447609

    Koen Verbeeck (2/15/2012)

    Jeff Moden (2/15/2012)

    Are you really serious? You're generating a report with nearly 6 million lines of output? Who's going to read THAT?

    Usually the business imports the results into an Excel sheet and do some filtering there.

    Because you know, TSQL doesn't have WHERE clauses and parameters...

    😀

    --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)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

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