Report Speed Tips 12

[lbass]

I would like to collect your ideas on improving report speed. I have to chuckle when I see people complaining about reports that take 20 seconds. We have a statewide database with millions of records, hundreds of tables, and an old inefficient server, and if we can get our reports to run in under 15 minutes, we think we have a fast report. We also are unable to upgrade our clients, meaning we can't use the optimal native driver.

The following are some strategies for improving speed that I've picked up, but I would like to hear about other people's methods to expand (or correct) upon this list:

1-Pass all elements of the record selection criteria to the SQL statement.
a- Do not use (most) formulas for record selection
b- Follow guidelines in SynapseVampires FAQ
2-Perform grouping on server (in our case, it's faster NOT to, though)
3-Link tables on indexed fields whenever possible.
4-Select on indexed fields when possible.
5-Remove unused tables, unused formulas, unused running totals from the report.
6-Use conditional formulas instead of running totals when possible.
7-Use conditional formulas to return a desired field result or not, instead of using suppression to eliminate unwanted records.
8-If using running totals, evaluate the minimum number of times, i.e., on change of group instead of for every record, if this is an option.
9-Group on indexed fields when possible (not sure, just surmising)
10-Group on database fields instead of formulas when possible (not sure, just surmising)
11-Avoid subreports (in most cases), although they may result in a faster report if they eliminate significant row inflation.
12-Using a command as the datasource will be faster than using CR to link tables, select records, etc.
13-If using a command as datasource, develop parameters within the command.
14-Link tables by selecting a lead table that has one indexed record to link to tables containing multiple corresponding records.
15-Suppress unnecessary sections; suppress or hide details to facilitate grouping on the server.
16-Avoid nested formulas.
17-Whenever possible, limit records through selection, not suppression.
18-Use SQL expressions to convert fields to be used in record selection instead of using formula functions.

I'm sure I've missed many obvious concepts. Please add any you can think of. Links to other sources are welcome as well.

Thanks for contributing!

-LB

 

[synapsevampire]

Robbie: I agree. I tend to use SQL Expressions in lieu of formulas, but prefer to do all of this on the database itself within Views/SPs.

80 formulas in a report is probably going to prove punishing and many of those can probably be converted to SQL Expressions which should speed things up, and I pity anyone forced to work in those enviromnets, that's when it's time to find a client/amployer that considers business intelligence critical to their futture

-k

 

[RobbieB]

Do you create then entire report using a command and create the needed "formula" in the SQL for the command?

 

[synapsevampire]

I try to avoid using Command Objects, that's embedding rules in the client,

My preference is to generate Views or SPs to base the report on. Then if something changes, I tweak the View/SP and may not have to touch the report at all.

Second might be to use a Command Object, it depends, sometimes I prefer the solution below over this.

Third would be the Crystal GUI and SQL Expressions.

-k

 

[lbass]

Would checking or unchecking "Perform Query Asynchronously" in file->report options have an impact on speed? I'm not exactly sure what this does. I think I read somewhere that it allows the user to stop the report at certain points before completion or something like that.

In general, are there certain report options or options settings that enhance speed other than the obvious "Use indexes of server for speed"?

-LB

 

[synapsevampire]

Are you certain that you can't use the Native connectivity with your Oracle client?

That would be a quick performance leap.

Asynchronously does mean that you can interrupt processing from my memory, you shouldn't see any performance gains there.

-k

 

[lbass]

SV,

As I mentioned in a different thread, our local client is Oracle 8, and the Oracle Server driver option isn't available. I did try to research what would be involved in upgrading to an Oracle 9 client (cost, etc.), but gave up as I wasn't sure what I was looking at on the Oracle site, and then I had a conversation with a colleague about the fact that everyone else in the state is using Oracle 8 and since we are developing reports to be used statewide, it made sense to develop them based on a driver that works with Oracle 8.

I'd like to followup another comment you made. In an earlier post you mentioned: "rearranging a WHERE clause can change performance radically." I was under the impression that CR automatically optimized SQL statements developed with the CR GUI, but recently ran into a situation where I added criteria to the beginning of the selection formula, and discovered that part of the remaining formula no longer was passed to the SQL. I then moved the new criteria to the end of the formula, and then all criteria passed (no difference to parens or anything)--which made me think that the order of the clauses in the formula DO matter.

In the link you provided re: Oracle optimization, when discussing optimizing SQL, the author suggests that in the From clause (e.g., if writing a command), you should start with the table that has the most records (if I'm reading this correctly). I guess I thought you should lead with a table that has one record that is then linked to those with many records.

Are there specific rules about the order of tables in the from clause of commands or about the order of clauses in the where part of a record selection formula in the GUI or in a command?

Anyone with comments please jump in.

-LB

 

[synapsevampire]

I've alluded to this in my FAQ on optimizing the SQL that especially with older versions it's part Voodoo to get the Record Selection set up.

I spent weeks trying to figure out the pattern, and there just isn't a discernible one.

The point, of course, is that you must always get the SQL to pass, but aside from that, the order in which the WHERE clause passes can improve performance, but this has more to do with indexes and the optimizer than simply the number of the rows in tables. So rearranging the record selection should alter how the where clause is generated, so don't be shy about switching things around and testing. If you find that in one configuration it doesn't pass, you can probably still get it todo so in that order, it just takes lots of patience and creativity. Really, it's Voodoo...

As for the Oracle 8 client not allowing for native connectivity through CR XI, I didn't recall that. I guess you mean that Oracle Server doesn't show as an option, and I'm not surprised.

Anyway, rearrange the record selection in different configurations in each report and test for gains.

Also one can check the execution plan on the Oracle server to see whether you are using full table scans, indexes, etc.

Getting a sharp Oracle dba proficient in tuning (this is rare, most think that they are) involved is critical.

-k

 

[lbass]

Thanks!

-LB

 

[cmmrfrds]

lbass, I believe Oracle 8 uses the CBO (cost based optimizer) as it does in Oracle 9 and up. I spend a fair amount of time optimizing Crystal queries in Oracle 9.2, and the CBO does not care about the order of the tables in the FROM clause. It uses table statistics, indexes, size and other information to select the optimized way to run the query. It is very important to keep the statistics current on the indexes as well as the tables. Make sure your DBA has a schedule for running the statistics. In addition to views and materialized views, our report writers frequently code the SQL in Command Objects. It is a good idea to store the Command Objects in the Repository so they are reusable by other report writers and other reports.

 

[synapsevampire]

cmmrfrds: Interesting that you'd suggest that queries be stored in a proprietary fashion taher than as Views or Stored Procedures, why is that?

I usually give the reverse advice, and occasionally bump up against Oracle dbas who don't want additional objects in the database, but of course to truly sinmply reusablility, the repository is a bad idea as tools other than crystal will have to go through the Crystal API to use them.

I'm sure Business Objects appreciates your thinking, however Mr. Elison probably would not.

Sounds like you understand databases well, I hope that you'll make it a habit to contribute here.

-k

 

[cmmrfrds]

synapesvampire, I am not opposed to using SP's and Views. In fact, if I had write permissions on the database, I probably would use views in most cases. However, I suspect most Crystal Report writers need to request these of the DBA and as you point out there are DBA's that don't like the proliferation of views for maintenance reasons or not wanting to be bothered. Putting the SQL in the Repository at least centralizes the sql logic and puts it under the control of the report writer.
My thinking is the the business logic/rules are the responsibilty of the report writer and not the DBA, so the report writer should have some flexibility in controlling this logic (i.e the SQL).

 

[synapsevampire]

cmmrfrds: Yours is a common take on this, and I agree that adding SQL to the repository beats not having a centralized approach.

But it's akin to having business rules embedded in application development.

In a small shop without competent database programmers and BAs you're limited, and go with what you have.

And reporting tends to be the redheaded stepchild of IT anyway, little justification or forethought is applied, yet it's the foundation for upper management in making critical business decisions.

That's a catastrophic disconnect, and explains most contracts I take that need to improve efficiencies.

To emphasize this point, I often use the simplified analogy that you have 2 ways to supply the prices for your parts, you can hardcode the values in code somewhere, or you can create a lookup/reference table.

Obviously you'd prefer the latter. Yet people often think very little about hardcoding other business rules into a proprietary layer, instead of taking the time to make them part of the database or an appropriate data mart so that all tools use the same rules, and enjoy simplified maintenance.

True, it requires that you measure twice and cut once, which is analogous to excellent, and too rare in IT management.

Everything that you store in the repository or in a BO View/Universe can be done on the database, and then it's available to all tools and you have a unified layer of business rules.

The notion that a report writer is competent enough to write SQL confirms that the dba thinks them competent enough to write Views/SPs anyway, it is afterall, all SQL executed on the database (OK, some other languages such as Java in SPs on Oracle can be used, but you get the point).

The fact that they allow software developers to use pass through SQL smacks of short term resource planning and management. A few here and there won't significantly impact performance, but it snowballs, and so the DBAs require more hardware and workarounds, and it continues to feed on itself, spiralling into a hodge podge of disparate, inefficient code that requires constant maintenance and additional hardware. Plus the method for extracting the data can vary between reports then, further complicating maintenance.

BAs, database programmers and software developers are all different titles for a valid reason.

Bring me the best report writer and show me her/his work, and I'll bring the best BA, database programmer and report writer and this theory that the report writer should have flexibility in implementing the business rules using SQL will prove optimistic.

I've had this discussion with the top IT people in the top firms for over 10 years regarding implementing Crystal. and even longer for software in general, and listened to the best arguments, and not once have they decided that in the best scenario a report writer should have this responsibility, it's stopgap and demonstrates poor management.

It's true that under resourced shops with unrealistic timeframes for their projects might be able to hit a few goals earlier on by this approach, but long term it's an expensive workaround to implementing quality software.

-k

 

[elsenorjose]

LB,

11-Avoid subreports (in most cases), although they may result in a faster report if they eliminate significant row inflation.

If you must use subreports, opt for on-demand subreports, if possible. Then, Pass 2 of the multi-pass flow only needs to happen during the drill down.

I've also read that using arrays instead of subreports can also help performance although I'm not quite sure how to use arrays that well so I can't elaborate. I'll try to find the link to the article.

 

[dbeleznay]

One other idea that I haven't seen mentioned yet that speeds up fetching the page n of m generation in crystal reports starting in version 8 is to turn off the can grow option for objects and sections in the report that you are not expecting to grow.

If all of your field heading objects, and field objects which you know should be one line high do not have the can grow flag set then Crystal Reports is able to skip over them when its working on calculating the page n of m.

Also if you are just using a formula to concatenate two fields it is faster to embed the fields in a text object.

 

[synapsevampire]

Interesting observations, dbeleznay.

Avoiding formulas in general (using text objects is one way, you can also concatenate in a SQL Expression) makes sense, add to that minimizing applying any formatting attributes makes sense as well, thanks for the tips!

-k