Brandie Tarvin (5/26/2016)
I need a little help to be pointed in the correct direction. We have a report that has been doing poorly since our upgrade from SQL 2008 to 2012. I can optimize the code with quick fixes on the proc end, but Crystal Reports is still taking forever to return data from previous financial years (2015, 2014) to the point where it's losing the connection to the server.After the developers and I dug into it (note: the reports were designed by the reporting team, not us), we've come to the conclusion that no matter how well I optimize the code in the back end, there's still those extra minutes being hogged by Business Objects trying to pull the data to the front end. A dev suggested that this means we need a structural overhaul. I'm inclined to agree with him.
I'm not sure the best way to proceed (i.e., suggest to the reporting team). Whether I should suggest overhauling the base tables in their datamarts or to suggest they start with their BO universes. But if I do suggest fixing the SQL tables, what suggestions do you have for optimizing read performance using a year-based plan? These datamarts are very wide, so adding a PostingYear column might not be a good idea, but I'm certain it's better than partitioning (which wouldn't work for performance anyway).
Thoughts? Links?
1) Business Objects SUCKS BADLY from a SQL Server standpoint. I have seen several implementations and they were all total dogs. There is only so much you can do to make them perform better too (but sometimes that is enough).
2) How long does SSMS take to return the results that are making Crystal time out? What if you return the entire result set to variables (thus keeping it on the server)?
3) Can't you just increase the timeout??
4) It's been a LONG time since I have played with Crystal, but does it have an option to consume records via the client side instead of RBAR? Are you seeing async_network_io waits while it the report running from Crystal?
5) You didn't give us much to go on to help with any suggestions or refactors.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service