February 15, 2012 at 6:16 am
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
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
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
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
Change is inevitable... Change for the better is not.
February 15, 2012 at 12:49 pm
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
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
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy