Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I have a dilemma


I have a dilemma

Author
Message
Oksana March
Oksana March
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 317
Hi everyone,
I have a dilemma and need your advice.
We have a customer that runs a report that retrieves very detailed data and takes about 5 minutes to complete for a period of 1 day. The query is optimized and the right indexes are set up, it takes that much because it's a lot of data. This president of the client company tried to specify a date range of 1 year and of course the query was just running and running. He complained the query doesn't work. I tried to explain that this report lists every transaction and it's not designed to be run for more than 1 day but he insists to run it for one year. How would you explain it to this big boss politely in an email message?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16574 Visits: 17018
Oksana March (11/5/2012)
Hi everyone,
I have a dilemma and need your advice.
We have a customer that runs a report that retrieves very detailed data and takes about 5 minutes to complete for a period of 1 day. The query is optimized and the right indexes are set up, it takes that much because it's a lot of data. This president of the client company tried to specify a date range of 1 year and of course the query was just running and running. He complained the query doesn't work. I tried to explain that this report lists every transaction and it's not designed to be run for more than 1 day but he insists to run it for one year. How would you explain it to this big boss politely in an email message?


Unless you are talking about millions and millions of rows being returned I would question that "The query is optimized and the right indexes are set up". 5 minutes seems a bit long for a daily report.

How many rows would be returned from this query if run for a whole year. Maybe you can offer alternatives to this data. Maybe offer to put this in a spreadsheet or some other format that will make it usable. If there are millions and millions of rows I doubt there is much they would be able to do with it anyway. Often time in situations like this they just don't understand the quantity of data and once they do they are perfectly content with samples, aggregate, etc...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45113 Visits: 39916
Oksana March (11/5/2012)
Hi everyone,
I have a dilemma and need your advice.
We have a customer that runs a report that retrieves very detailed data and takes about 5 minutes to complete for a period of 1 day. The query is optimized and the right indexes are set up, it takes that much because it's a lot of data. This president of the client company tried to specify a date range of 1 year and of course the query was just running and running. He complained the query doesn't work. I tried to explain that this report lists every transaction and it's not designed to be run for more than 1 day but he insists to run it for one year. How would you explain it to this big boss politely in an email message?


So, how many transactions are there for a year?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Does the query do anything other than return raw data from one table? Aggregations (min, max, avg, that kind of thing), running totals, pulling data from complex multi-table structures; anything like that?

If so, you might want to run the query for a year's data, and dump the results into a temp table (or a "real" table), then point a copy of the report at that data instead of the query. That way, all the hard work for the query is done before the report tries to run it, and the report will almost certainly be much faster.

(Data warehousing, in case you're familiar with that, is what I'm talking about here.)

Is that an option?

Or will the 1-year report be so many pages of data that the server simply can't run it? I've seen a report that was intended for daily data, which ran around 30-50 pages per day of data, where someone tried to run it for a year instead of a day. We managed to get it to render the report, all 18,000 pages of it, as a PDF. Took a few days on a standby server. The manager insisted on this being done, right up till the point where it started to spew out of his printer and he was told it would take 2 days to finish printing. His eyes got really big, and he changed his mind about trying to read more pages of report than there are in ten copies of War and Peace. A fun time was had by all. But, till he actually got to see it with his own eyes, and get the scope that way, it wasn't real to him that what he thought he wanted wasn't what he actually wanted.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Oksana March
Oksana March
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 317
Ok, the answer is a little confusing. It IS millions of transactions but it will not be that much in the future. This is a camera that takes many pictures per second and identifies objects. Except now it's in the test lab, so every snapshot identifies some objects. It is not possible to remove these objects from the lab. In the real world, it will only identify objects if they appear, so it will be much less transactions.
But the report we are developing now.
Hope I have explained it well.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
How much work does the query for the report do? Can you post the table definition(s) and the query?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search