November 5, 2012 at 10:02 am
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?
November 5, 2012 at 10:27 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 10:49 am
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
Change is inevitable... Change for the better is not.
November 5, 2012 at 11:35 am
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
November 7, 2012 at 6:43 am
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.
November 7, 2012 at 8:15 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply