When you run a SQL statement in Oracle, all you get is the raw data.  When you run it in Crystal, Crystal formats it and renders it.  This still should not take 20 minutes.  There are a couple of things that can be done in Crystal to speed things up, there are also a couple of things to avoid.

 

1.  An ODBC connection WILL slow down the query because ODBC means there's an additional layer of code that needs to process the data before returning it to the report.  If possible, use the native Oracle connection that's available in Crystal.

 

2.  In Crystal select "Report Options" from the edit menu.  Make sure that "Use Indexes or Server For Speed" is turned on.  This primarily affects reports that are created from individual tables instead of from commands and covers this setting for this report only.

 

3. Select "Options" from the Edit menu.  Go to the Database tab and turn on "Use Indexes or Server for Speed" and "Perform Grouping On Server".  This will turn these on for all new reports that you create in the future.

 

4.  If you're using a command, sort your data in the command in the same order that you're going to group and sort your report.  This way Crystal won't have to do the work in memory.

 

5.  Don't use more than one command or a command with one or more tables.  In these situations, Crystal will do all of the linking between the result sets in memory rather than having the database server do it.  Instead, work all of your SQL into a single command.

 

6.  Avoid sorting or grouping on a Crystal formula.  If Crystal can't pass the formula to the database, it will sort and/or group in memory.

 

7.  Avoid filtering on a Crystal formula.  If Crystal can't pass the formula to the database, it will pull ALL of the data to the workstation and then filter it in memory.

 

NOTE:  If you're using tables instead of a command, you frequently can create SQL Expressions to perform grouping, sorting, and/or filtering from items 6 and 7.  If you're using a command, SQL Expressions are an not available option because you should do that processing in the SQL of your command.

 

8.  Avoid subreports, especially if they're not on-demand.  Every time Crystal gets to a section that contains a subreport, the query in the subreport is re-run on the database.  Data is NOT cached and then filtered based on the link.

 

-Dell

 

Crystal cannot work this way - it pulls in ALL of the rows of data so that it can process them. 

 

A better way to judge the "speed" of the query in Oracle is to run it then "ctrl-End" to go to the end of the data set and see how long it then takes to go to the end of the data.  Then you add together the initial run time plus the time it takes to go to the end of the data.  It will still be faster in the Oracle tools, but it gets you a clearer picture of what's happening.

 

-Dell