Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I have a dilemma Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 10:02 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 9:07 PM
Points: 43, 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?
Post #1381180
Posted Monday, November 5, 2012 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
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)
Post #1381198
Posted Monday, November 5, 2012 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381206
Posted Monday, November 5, 2012 11:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1381221
Posted Wednesday, November 7, 2012 6:43 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 9:07 PM
Points: 43, 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.
Post #1381955
Posted Wednesday, November 7, 2012 8:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1382012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse