May 26, 2016 at 9:49 am
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?
May 26, 2016 at 10:41 am
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
May 26, 2016 at 11:07 am
TheSQLGuru (5/26/2016)
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).
HOO-yeah. But I just found out for sure that they're not going through BO. They're going directly to the report-copy versions of the databases. Yes, databases plural. But only to touch specific tables.
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)?
11 seconds for the detail proc. 34 seconds (after I removed the 3 functions to figure out the DAY or MONTH or YEAR on a date field) for the summary proc, which apparently gets run twice. Once for the summary and once for another part of the report. The rest of the 7+ minutes is spent prettifying the report.
3) Can't you just increase the timeout??
Yeaaaaaahhhh, but I don't want to. A report that's taking over 7 minutes to pull is a report that needs to be fixed, not coddled so that it can break even worse at a later date. Besides, I wouldn't be the one fixing it. @=)
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?
I'd have to check, but the procs are pulling set based data, not RBAR data. So I don't think that's the problem. In fact, given my own long-ago Crystal experience, I'm thinking part of the issue lies in how the different sections of the report (and subreports) have probably been designed and interlocked. Plus the report is rendering to .pdf, not Crystal native format. So I think that's adding some addition processing time.
5) You didn't give us much to go on to help with any suggestions or refactors.
No I didn't. I'm just looking for general suggestions on where I can do more research more than I'm looking for a specific answer. I want something general (links are helpful) that I can pass on to the team doing the fixes. But since you ask...
The summary proc touches 4-5 databases and about 2-3 tables in each of those databases. All databases are on the same server. There are three sections of code. The first two set up temp tables, the third is a giant UNION ALL with three sections to pull data. I'm thinking the first thing the team needs to do is (shudder) maybe pull things together in a flat file table on a daily or weekly process as part of an early morning job and then query off of that so that the report isn't actually processing the data, it's just pulling it.
I don't want to recommend this because the report database has enough flat file tables as it is, most of which are just replicating data available in other places with additions of a few other things. On the other hand, I've banged my head against this wall so hard that I can't see any other viable structural fixes.
There is a strong possibility that this is just the tip of the iceberg. That there may be other reports with the same issues. So if I can just get some "look into these techniques" links or keywords, I would appreciate it. The suggestions may or may not work, but at least the information is something I can pass along to the people doing the work.
May 26, 2016 at 11:26 am
A) If the report queries are finishing so quickly then NOTHING you do to "make them go faster" will help, right? That 7 minutes OUTSIDE SQL Server has to be addressed. That can possibly be achieved by checking settings for Crystal. Maybe get a faster client machine? But as for RBAR, many things execute a query as set-based but then do a server-side cursor (SSMS does this IIRC) to actually fetch one row at a time. I would also have them switch the report to be the SIMPLEST rendering possible and check the timing of that.
B) Are you absolutely certain the queries are only getting run the few number of times you think they are? Iterative calling for the child report (similar to the 1+N mess ORMs often do) would be disaster obviously.
C) Thinking way outside the box, is the report some how stuck in "debug" mode?
BTW, good to hear from you again! How are things going? 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply