Performance using Calculated Parameters in Select 3

[thorntone]

I've got a performance issue when using a calculated value in my select statement. I'm inexperienced with Crystal, but experienced with both SQL and other reporting tools.

The database table I'm reporting against has a time field which is the number of seconds since a point in time. The intention of the report is to select all values up to 28 days ago to the present. The conversion of the current date to the appropriate starting point works well, but doesn't get passed to the database, rather a table scan is performed, with the select then compared to every record in the database. Hard coding the intergers that represent the starting point results in a relatively fast query (obviously using the database index). How can I structure the query in Crystal to pass the calculated value to the database and thereby using the indexes?

BTW, the identical query works in MS Access as I would desire, so it's not an ODBC/database issue.

Thanks,
Earl

 

[MalcolmW]

I'm too lazy to try to keep up with the nuances of how to get (and keep) SCR to actually apply a filter at the database level. As I am comfortable with SQL, I just write a stored proc, and report from that dataset. In a case like you describe, I would provide a numeric parameter for the sp as well.
If Access is your database, then making an Access Query would solve the problem at the database level as well.
Malcolm

 

[kenhamady]

I will bet that your selection formula is using a function. This is one of the things that will prevent the rule from being passed to SQL. Here are two tips if you don't have the option of using the above suggestion.

1) If you can create an SQL expression to do the conversion, instead of a formula, You can use the SQL Exp in the select expert and get faster performance.

2) You can also add a fixed literal rule to the select formula to always eliminate records that are older than 1/1/2001, since you should never need those again. Use this rule in addition to the rule you have, and it will keep the report from needing a full table scan. At most it will start with the beginning of this year, and then select based on your parameter. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[emcevoy]

Hi there

I have written an faq on report processing speed that might be of Interesting reading for you.

Hope you find it helpful. E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com