SQL brings back data quickly, parameters stall out process

[JMG1019]

I am using a SQL statment to retrieve data for my report. If the Crystal report is run with just the SQL statement it takes less than 10 minutes to run. When I add parameters to the Crystal Report (I'm using XI), the report takes approximately 45 minutes to run.

Any suggestions on why parameters would slow the report?

 

[synapsevampire]

Any report that goes against a database which uses the SQL language will use a SQL statement against the database, so stating that you're using a SQL Statement doesn't tell us anything about how the report was created, the type of database or the connectivity used.

Try posting:

Using the Crystal GUI, a Command object, or however you are creating this SQL statement
Type of Database/connectivity used

I'm guessing that you aren't passing the filtering to the database, so all rows are returned to Crystal, and then Crystal is filtering out the data, hence to delay, but we need technical information to properly assist you.

You may find that whatever you are placing in the Report->Selection Formula->Record isn't being passed to the database.

-k

 

[JMG1019]

I add my SQL (which queries our Oracle database) through the use of the 'Add Command'. All rows are returned to Crystal and the parameters do the filtering. Other flexible parameter reports generally don't take longer to run, they actually run faster. When the query is added to Crystal and run, it takes less than 10 minutes.

Two of my parameters deal directly with information brought in by the SQL statement. The third allows the user to sort the data by dates. The user specifies which field to sort by and this information is passed to a formula which is then used in the Record Sort Expert to define sort order.

I have removed all unused parameters and have also removed the sorting parameter and have found that the report is still taking 30 - 45 minutes.

Please let me know if more information is needed.

 

[synapsevampire]

So you've created and applied the parameters within the Command building area itself, right?

If not, that's what you should be doing or it won't filter on the database.

If there are lots of records, and you're doing local sorting then it could slow things significantly.

Yoiu may be able to do the sorting dynamically, and you mayh find that you're better served to use a View or SP on the database, or even the Crystal GUI.

I've not tried it, but you migh be able to do the sorting dynamically using the parameter in the SQL statement of the command using a decode, as in sort by {?sortparameter}

-k

 

[Turkbear]

Hi,
Have you run an 'explain plan' against the SQL ( both flavors, the one in your Command Object, and the one Crystal creates?


I also want to second synapse's note about being sure the parameters are part of the Command Object and not just used by Crystal once the data from the command's Query is returned..


If you have access to some DBA tools you could check to see what Oracle is actually using as its query..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[JMG1019]

I hadn't tried using the parameter selection in the 'Add Command' window. That worked much better than utilizing the selection criteria in Crystal.

Thank you for your help.

 

[Turkbear]

Hi,
It helps to think of the 'Command Object' as a separate request to the database ( actually it is unrelated to the Crystal Report at time of execution) and, therefore, you need to give it all the information it needs to return just the dataset you need..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think that if you add the parameters in the Crystal Report proper, instead of within the command, the parameters are applied locally. If you apply them within the command, they are passed to the database.

-LB