SQL Clone
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-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60837 Visits: 17954
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 Modens 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 Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209341 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56117 Visits: 9730
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-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56117 Visits: 9730
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