The story is like this, we have a horrible report, This report contains 2 sub-reports: one is for the overall chart which is fine, the 2nd sub-report contains 4 matrices, each matrix has date, week, and quarter column groups, and has business perspectives at row groups.
The report is a campaign to date report, which shows current quarter in weekly breakdowns, and suppress previous quarters into Quartly Total. Therefore at the beginning of each quarter the subscription is fine, but when it is close to end of the quarter, 9 out of 10 the execution failed.
Long in short, what I did are
- Identify the the problem: in this case, it is a matrix to show states, retailers, offers, and rates by date.
- Isolate the problem: I create a dummy report for this matrix so I can compare the performance to my solution
- Work out the solution: I then create a new report by re-write the query and re-design the groups of the matrix
- Compare the performance: one picture worth thousand words
The tricky thing is when I wrote the backend query, I actually made it more complicated. It is because in this particular case, query is fast enough to deliver the data to report server, but processing data is too slow due to complicated grouping conditions in the matrix. So I made some groups at the query level, which slows down the data retrieval time, but improve processing time a lot.
Now some my personal ideas:
Firstly, real world is much tough then the book. We know rules such as "query the data only when you need to", or "retrieve the data you need only". But in real world you have to work it out within business scope.
Secondly, having a big picture is impotant. In this case the report is scheduled as a subscription to run at 6:00AM. So blocking is not a problem when made the query complicated.
Finally, "Best result" is enviornment oriented. For this particular report, sacrificing few seconds in query time gives you 10 times benefits at processing level.
No comments :
Post a Comment